Sunday 5 May 2013


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  


LSQL  
   GS$VC2MAXSIZE  CONSTANT NU

--MAX SIZE OF VARCHAR2 IN 
PMBER := 32767;  

   --WRITE CLOB FILE TO PHYSICAL FILE  


            IN_DIR_NAME   VARCHAR2,  
              

PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     CLOB,  
     
     IN_FILE_NAME  VARCHAR2);  

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


RCE_CODE1 (IN_OBJECT_T

RETURN VARCHAR2;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SO
UYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  


ECT_TYPE  VARCHAR2

RETURN CLOB;  

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



JECT_NAME  VARCHAR2,  
                IN_SC

--GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_O
BHEMA     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  


OB,  
                  IN_DIR_NAME   VARCHAR2,  
  

PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     C
L                IN_FILE_NAME  VARCHAR2)  
   IS  
    LUTL$FILE_HANDLER  UTL_FILE.FILE_TYPE;  


L$FILE_HANDLER := UTL_FILE.FOPEN (IN_D

LN$OFFSET      NUMBER DEFAULT 1;  
    LC$BUFFER      VARCHAR2 (32767);  
   BEGIN  
    LU
TIR_NAME, IN_FILE_NAME, 'A');  
    LOOP  
      EXIT WHEN LN$OFFSET > DBMS_LOB.GETLENGTH (IN_CLOB);  


NDLER, LC$BUFFER);  
      LN$OFFSET := LN$OFFSET + 32767;  
    

LC$BUFFER := DBMS_LOB.SUBSTR (IN_CLOB, 32767, LN$OFFSET);  
      UTL_FILE.PUT_LINE (LUTL$FILE_H
A  LC$BUFFER := NULL;  
    END LOOP;  
    UTL_FILE.FCLOSE (LUTL$FILE_HANDLER);  
   END;  

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


TLENGTH (LCLOB$DUMY) 

RETURN VARCHAR2  
   IS  
    LC$RESULT  VARCHAR2 (32767);  
    LCLOB$DUMY  CLOB := EMPTY_CLOB ();  
   BEGIN  
    LCLOB$DUMY := IN_CLOB;  
    IF DBMS_LOB.G
E<= GS$VC2MAXSIZE  
    THEN  
      DBMS_LOB.OPEN (LCLOB$DUMY, DBMS_LOB.LOB_READONLY);  
      LC$RESULT := DBMS_LOB.SUBSTR (LCLOB$DUMY);  
      DBMS_LOB.CLOSE (LCLOB$DUMY);  


URCE CODE 

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 S
OUSING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SOURCE_CODE1 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  


CLOB$SOURCE_CODE :=  
      DBMS_METADATA.GET_DDL (IN_

IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    
LOBJECT_TYPE, IN_OBJECT_NAME, IN_SCHEMA);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE1;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  


RN CLOB  
   IS  
    LN$HANDLE      NUMBER;  
    LN$DUM

FUNCTION GET_SOURCE_CODE2 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RET
UMY      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.CLOSE (LN$HANDLE);  
    RETURN LCLOB$SOURCE_C

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)
;ODE;  
   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,  


TRUE;  
    CURSOR LCUR$SOURCE  
    IS  
 

IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB;  
    LC$START_TEXT    VARCHAR2 (50);  
    LB$EXISTS      BOOLEAN :=
       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);  


GTH (LC$START_TEXT),  
            LC$START_TEXT);  
      DBMS_

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,  
            LE
NLOB.  
      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  


t source code of TRIGGER MCIT_CMS.ADMIN_PROG_TRG in DBMS Output  
   LC$SO

--pri
nURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  


ER', 'ADMIN_PROG_TRG', 'MCIT

SEKHAR_SOURCE_CODE.  
      GET_SOURCE_CODE2 ('TRIG
G_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  

   --print script of TABLE  










_CODE.  
      GET_SOU

LC$SOURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  
      SEKHAR_SOURC
ERCE_CODE2 ('TABLE', 'ADMIN_PROGRAM', 'MCIT_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  



E (  
    SEKHAR_SOURCE_CODE.GET_SOURCE_CODE2 ('TAB

--Write Source code to $SOURCE_CODE\Sekhar.sql  
   MAHMOUD_SOURCE_CODE.  
   WRITE_CLOB_IN_FI
LLE', 'EMP', 'SCOTT'),  
    'SOURCE_CODE',  
    'Sekhar.sqlL');  





END;

The Result in DBMS Output It will create new file Sekhar.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  


nextval into :new.PROGRAM_ID from dual;  
 end  
 CREATE TABLE MCIT_

select ADMIN_PROG_SEQ
.CMS.ADMIN_PROGRAM  
 (  
  PROGRAM_ID           NUMBER     NOT NULL,  


 VARCHAR2(20 BYTE),  
  PROGRAM_OUTBOUND_CD  

PROGRAM_NAME          VARCHAR2(200 BYTE),  
  PROGRAM_INBOUND_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 Sekhar_SOURCE_CODE.GET_SOURCE_CODE2 you can build your script and use any procedure from package to get your source code.

Thanks
Sekhar
Posted by at

1 comment:

  1. Darren Demers16 April 2020 at 04:44

    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. bed sheets sale online , comforter sets full , gul ahmed bed sheets sale 2018 , bed sheet bridal , fancy bedspreads , cotton gadda shop near me , velvet blanket , cotton razai price , best sofa covers , pakistani lawn suits price

    ReplyDelete

Subscribe to: Post Comments (Atom)