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.
No comments:
Post a Comment