Generate Source Code Scripts of Database Objects

We use a lot of editors to display source code of Oracle Database objects like Toad, PLSQL Developer, Navigator and SQL Developer and others.

I have idea to do like this editors to generate source code of database objects (Table, View, Trigger, Functions, Package, Function, Procedure, ...... etc) using Oracle Database Data Dictionary views.

I can do this using using
1-ALL_SOURCE view which contains source code of package, package body, function, procedure, library, type, type body,java source only.
You can use others view to generate tables and indexes and constraint and database links and ..... etc, but at this post I only use ALL_SOURCE

2-DBMS_METADATA built-in package which contains procedure and functions that help me to get source code directly from database with less efforts.

I created MAHMOUD_SOURCE_CODE package with below procedures to get source code of database objects
1-GET_SOURCE_CODE1
   I use DBMS_METADATA.GET_DDL function
2-GET_SOURCE_CODE2 
   I use DBMS_METADATA procedure and functions to get source code
3-GET_SOURCE_CODE3 
   I use ALL_SOURCE view

Mahmoud_SOURCE_CODE package also containts
1- WRITE_CLOB_IN_FILE
    It saves CLOB variable in physical file at directory object
2-TO_CHAR
    It converts CLOB variable to VARCHAR2 variable



The code of MAHMOUD_SOURCE_CODE package specification

 CREATE OR REPLACE PACKAGE MAHMOUD_SOURCE_CODE  
  AUTHID CURRENT_USER  
 AS  
   --MAX SIZE OF VARCHAR2 IN PLSQL  
   GS$VC2MAXSIZE  CONSTANT NUMBER := 32767;  

   --WRITE CLOB FILE TO PHYSICAL FILE  
   PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     CLOB,  
                  IN_DIR_NAME   VARCHAR2,  
                  IN_FILE_NAME  VARCHAR2);  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  
    RETURN VARCHAR2;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SOURCE_CODE1 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  
   FUNCTION GET_SOURCE_CODE2 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  

   --GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  

 END MAHMOUD_SOURCE_CODE;  

The code of MAHMOUD_SOURCE_CODE package body

 CREATE OR REPLACE PACKAGE BODY MAHMOUD_SOURCE_CODE  
 AS  

   --WRITE CLOB FILE TO PHYSICAL FILE  
   PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     CLOB,  
                  IN_DIR_NAME   VARCHAR2,  
                  IN_FILE_NAME  VARCHAR2)  
   IS  
    LUTL$FILE_HANDLER  UTL_FILE.FILE_TYPE;  
    LN$OFFSET      NUMBER DEFAULT 1;  
    LC$BUFFER      VARCHAR2 (32767);  
   BEGIN  
    LUTL$FILE_HANDLER := UTL_FILE.FOPEN (IN_DIR_NAME, IN_FILE_NAME, 'A');  
    LOOP  
      EXIT WHEN LN$OFFSET > DBMS_LOB.GETLENGTH (IN_CLOB);  
      LC$BUFFER := DBMS_LOB.SUBSTR (IN_CLOB, 32767, LN$OFFSET);  
      UTL_FILE.PUT_LINE (LUTL$FILE_HANDLER, LC$BUFFER);  
      LN$OFFSET := LN$OFFSET + 32767;  
      LC$BUFFER := NULL;  
    END LOOP;  
    UTL_FILE.FCLOSE (LUTL$FILE_HANDLER);  
   END;  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  
    RETURN VARCHAR2  
   IS  
    LC$RESULT  VARCHAR2 (32767);  
    LCLOB$DUMY  CLOB := EMPTY_CLOB ();  
   BEGIN  
    LCLOB$DUMY := IN_CLOB;  
    IF DBMS_LOB.GETLENGTH (LCLOB$DUMY) <= GS$VC2MAXSIZE  
    THEN  
      DBMS_LOB.OPEN (LCLOB$DUMY, DBMS_LOB.LOB_READONLY);  
      LC$RESULT := DBMS_LOB.SUBSTR (LCLOB$DUMY);  
      DBMS_LOB.CLOSE (LCLOB$DUMY);  
    ELSE  
      RETURN 'You can''t convert CLOB to varchar2 because CLOB is bigger than maximum size of varchar2';  
    END IF;  
    RETURN LC$RESULT;  
   END;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SOURCE_CODE1 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    LCLOB$SOURCE_CODE :=  
      DBMS_METADATA.GET_DDL (IN_OBJECT_TYPE, IN_OBJECT_NAME, IN_SCHEMA);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE1;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  
   FUNCTION GET_SOURCE_CODE2 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LN$HANDLE      NUMBER;  
    LN$DUMMY      NUMBER;  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    LN$HANDLE := DBMS_METADATA.OPEN (IN_OBJECT_TYPE);  
    DBMS_METADATA.SET_FILTER (LN$HANDLE, 'SCHEMA', IN_SCHEMA);  
    DBMS_METADATA.SET_FILTER (LN$HANDLE, 'NAME', IN_OBJECT_NAME);  
    LN$DUMMY := DBMS_METADATA.ADD_TRANSFORM (LN$HANDLE, 'DDL');  
    LCLOB$SOURCE_CODE := DBMS_METADATA.FETCH_CLOB (LN$HANDLE);  
    DBMS_METADATA.CLOSE (LN$HANDLE);  
    RETURN LCLOB$SOURCE_CODE;  
   EXCEPTION  
    WHEN OTHERS  
    THEN  
      DBMS_METADATA.CLOSE (LN$HANDLE);  
   END GET_SOURCE_CODE2; 
 
   --GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB;  
    LC$START_TEXT    VARCHAR2 (50);  
    LB$EXISTS      BOOLEAN := TRUE;  
    CURSOR LCUR$SOURCE  
    IS  
       SELECT TEXT  
        FROM ALL_SOURCE  
       WHERE   OWNER = IN_SCHEMA  
          AND NAME = IN_OBJECT_NAME  
          AND TYPE = IN_OBJECT_TYPE  
      ORDER BY LINE;  
   BEGIN  
    DBMS_LOB.CREATETEMPORARY (LCLOB$SOURCE_CODE, TRUE);  
    DBMS_LOB.OPEN (LCLOB$SOURCE_CODE, DBMS_LOB.LOB_READWRITE);  
    FOR LREC$SOURCE IN LCUR$SOURCE  
    LOOP  
      LB$EXISTS := FALSE;  
      LC$START_TEXT := 'CREATE ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LENGTH (LC$START_TEXT),  
            LC$START_TEXT);  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LENGTH (LREC$SOURCE.TEXT),  
            LREC$SOURCE.TEXT);  
    END LOOP;  
    IF LB$EXISTS  
    THEN  
      LC$START_TEXT := IN_OBJECT_TYPE || ' not support in ALL_SOURCE View ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LENGTH (LC$START_TEXT),  
            LC$START_TEXT);  
    END IF;  
    DBMS_LOB.CLOSE (LCLOB$SOURCE_CODE);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE3;  

 END MAHMOUD_SOURCE_CODE;  
 /  

Run MAHMOUD_SOURCE_CODE package

I create WRITE_CLOB_IN_FILE procedure to write source code to physical file, the second parameter in this procedure is directory object name.

To create directory named SOURCE_CODE  use below command

 CREATE OR REPLACE DIRECTORY  
 SOURCE_CODE AS  
 'c:\temp\';  

We should grant read and write on directory to user who will use MAHMOUD_SOURCE_CODE package by below command

 grant read,write on SOURCE_CODE to SCOTT;  

Now let's run below test script and see the result 

 DECLARE  
   LC$SOURCE_CODE  VARCHAR2 (32767);  
 BEGIN  
   --print source code of TRIGGER MCIT_CMS.ADMIN_PROG_TRG in DBMS Output  
   LC$SOURCE_CODE :=  
    MAHMOUD_SOURCE_CODE.  
    TO_CHAR (  
      MAHMOUD_SOURCE_CODE.  
      GET_SOURCE_CODE2 ('TRIGGER', 'ADMIN_PROG_TRG', 'MCIT_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  

   --print script of TABLE  
   LC$SOURCE_CODE :=  
    MAHMOUD_SOURCE_CODE.  
    TO_CHAR (  
      MAHMOUD_SOURCE_CODE.  
      GET_SOURCE_CODE2 ('TABLE', 'ADMIN_PROGRAM', 'MCIT_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  

   --Write Source code to $SOURCE_CODE\Mahmoud.sql  
   MAHMOUD_SOURCE_CODE.  
   WRITE_CLOB_IN_FILE (  
    MAHMOUD_SOURCE_CODE.GET_SOURCE_CODE2 ('TABLE', 'EMP', 'SCOTT'),  
    'SOURCE_CODE',  
    'Mahmoud.sqlL');  

 END;  

The Result in DBMS Output It will create new file Mahmoud.sql in c:\temp and it will print in DBMS Output below paragraph

 CREATE TRIGGER MCIT_CMS.ADMIN_PROG_TRG before insert on ADMIN_PROGRAM  
   for each row  
 begin  
     select ADMIN_PROG_SEQ.nextval into :new.PROGRAM_ID from dual;  
 end  
 CREATE TABLE MCIT_CMS.ADMIN_PROGRAM  
 (  
  PROGRAM_ID           NUMBER     NOT NULL,  
  PROGRAM_NAME          VARCHAR2(200 BYTE),  
  PROGRAM_INBOUND_CD       VARCHAR2(20 BYTE),  
  PROGRAM_OUTBOUND_CD      VARCHAR2(20 BYTE),  
  COMM_DEPT_ID          NUMBER,  
  PROGRAM_OUTBOUND_BARCODE_TYPE VARCHAR2(2 BYTE),  
  PROGRAM_INBOUND_BARCODE_TYPE  VARCHAR2(2 BYTE)  
 )  

Please notice that I used in script Mahmoud_SOURCE_CODE.GET_SOURCE_CODE2 you can build your script and use any procedure from package to get your source code.

Thanks