------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 13:25 14-Nov-2021 (001)
--
-- DBT_RETRUSMETA - DATABASE TOOLS, RETRIEVE A USER METADATA
--
-- The DBT_RETRUSMETA PL/SQL anonymous block retrieves metadata for a user.
--
------------------------------------------------------------

SET LINES 500
SET PAGES 0
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET SQLNUMBER OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON FORMAT WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

DECLARE 
USERNAME VARCHAR2(40)  := UPPER('&USERNAME');
LINE     VARCHAR2(100) := CHR(10) || RPAD('-',40,'-') || CHR(10);
BEGIN
   
  BEGIN
    
    -- FETCHING A USER METADATA
    
    DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING USER METADATA' || LINE);
    DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_DDL('USER',USERNAME));
    
      -- SYSTEM PRIVILEGIES 

      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING SYSTEM PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE( 'The user ' || USERNAME || ' does NOT have any system privilegies.');
      END;
      
      -- ROLE PRIVILEGIES
      
      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING ROLE PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT have any role privilegies.');
      END;

      -- OBJECT PRIVILEGIES
      
      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING OBJECT PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT have any object privilegies.' || CHR(10));
      END;
      
  EXCEPTION
    WHEN OTHERS THEN
      IF USERNAME IS NULL THEN 
        DBMS_OUTPUT.PUT_LINE('The USERNAME is NOT defined. Try again.' || CHR(10));
      ELSE
        DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT exist.' || CHR(10));
       END IF;
  END;

END;
/