Export/Generat user scripts in physical directory in Oracle Database.

In this article I am try to export user scripts in my physical directory. I have use an external table for maintain schema which I want to export.  


--- CREATING DIRECTORY FOR WRITE FILE

CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

--- GRANT READ AND WRITE PERMITION TO SPECIFIC USER

GRANT READ, WRITE ON DIRECTIORY DATA_DIR TO TEST_USER;

-- CREATING EXTERNAL TABLE FOR COLLECT USER INFORMATION

CREATE TABLE EXT_TABLE
(
   USER_NAME         VARCHAR2 (200),
   REPLACE_ALLOWED   VARCHAR2 (10),
   COMMENTS          VARCHAR2 (300)
)
ORGANIZATION EXTERNAL
    (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'data.log'
BADFILE 'data.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION('USER_REPLACE.csv')
)
REJECT LIMIT UNLIMITED;

-- CREATING PROCEDURE TO WRITE USER SCRIPTS

CREATE OR REPLACE PROCEDURE SP_USER_PRIVILEGES_SCRIPTS
(P_DIRECTORY VARCHAR2,P_FILE_NAME VARCHAR2)
IS
  V_CHNK_SIZE PLS_INTEGER;
  V_CLOB_USER CLOB;
  V_CLOB_DATA CLOB;
  V_OUTPUT UTL_FILE.FILE_TYPE;
  V_FILE_NAME VARCHAR2(1000);
 
  PROCEDURE USER_FILE_WRITE(P_WRITE_TAG VARCHAR2) IS
      BEGIN
        V_FILE_NAME:=P_FILE_NAME||'_'||P_WRITE_TAG||'.SQL';
        V_CLOB_DATA:=NULL;
       
        FOR INDUSR IN (SELECT USER_NAME  FROM EXT_TABLE WHERE REPLACE_ALLOWED='Y') LOOP

         BEGIN
            SELECT REPLACE(REPLACE(DBMS_METADATA.GET_DDL(P_WRITE_TAG, INDUSR.USER_NAME),'"',' '),'
 ','')||';'  INTO V_CLOB_USER
            FROM DUAL;
          END;
          V_CLOB_DATA:=V_CLOB_DATA||V_CLOB_USER;
         END LOOP;
        
          V_OUTPUT := UTL_FILE.FOPEN( P_DIRECTORY, V_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

          V_CHNK_SIZE := 3000;
          FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
          LOOP
           
            UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
             UTL_FILE.FFLUSH(V_OUTPUT);
          END LOOP;
       
          UTL_FILE.FCLOSE(V_OUTPUT);
     END;
    
   PROCEDURE GRANT_FILE_WRITE(P_WRITE_TAG VARCHAR2) IS
  BEGIN
    V_FILE_NAME:=P_FILE_NAME||'_'||P_WRITE_TAG||'.SQL';
    V_CLOB_DATA:=NULL;
 BEGIN
        FOR INDUSR IN (SELECT USER_NAME  FROM EXT_TABLE WHERE REPLACE_ALLOWED='Y') LOOP

         BEGIN
            SELECT REPLACE(REPLACE(REPLACE(DBMS_METADATA.GET_GRANTED_DDL(P_WRITE_TAG, INDUSR.USER_NAME),'"',' '),'
 ',';'),' . ','.')  INTO V_CLOB_USER
            FROM DUAL;
          END;
          V_CLOB_DATA:=V_CLOB_DATA||V_CLOB_USER;
         END LOOP;
  END;
  V_OUTPUT := UTL_FILE.FOPEN( P_DIRECTORY, V_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

  V_CHNK_SIZE := 3000;
  FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
  LOOP
   
    UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
    UTL_FILE.FFLUSH(V_OUTPUT);
  END LOOP;
  UTL_FILE.FCLOSE(V_OUTPUT);
 END;

 BEGIN

  USER_FILE_WRITE('USER');
  GRANT_FILE_WRITE('ROLE_GRANT');
  GRANT_FILE_WRITE('SYSTEM_GRANT');
  GRANT_FILE_WRITE('OBJECT_GRANT');
 
END;
/

-- EXECUTE PROCEDURE AND COLLECT SCRIPTS FROM DIRECTORIES

EXEC SP_USER_PRIVILEGES_SCRIPTS('DATA_DIR','TEST_SCRIPTS');