Last active
March 31, 2020 21:53
-
-
Save AndyDaSilva52/e3eb491453bcfac811ef2c24b2f2968b to your computer and use it in GitHub Desktop.
Oracle - Procedure to Export DDL (SELECT * FROM ALL_OBJECTS) - Write in Directory of Oracle
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace PROCEDURE P_EXPORT_DDL | |
( | |
pOWNER IN ALL_OBJECTS.OWNER%TYPE DEFAULT 'SYS', | |
pOBJECT_TYPE IN ALL_OBJECTS.OBJECT_TYPE%TYPE DEFAULT 'PROCEDURE', | |
pOBJECT_NAME IN ALL_OBJECTS.OBJECT_NAME%TYPE DEFAULT 'P_EXPORT_DDL' | |
) AS | |
data CLOB; | |
fname varchar2(256); | |
vDiretorio ALL_DIRECTORIES.DIRECTORY_NAME%TYPE; | |
vOBJECT_TYPE ALL_OBJECTS.OBJECT_TYPE%TYPE; | |
BEGIN | |
vOBJECT_TYPE := | |
CASE pOBJECT_TYPE | |
WHEN 'PACKAGE BODY' THEN 'PACKAGE' | |
WHEN 'TYPE BODY' THEN 'TYPE' | |
--When ['JOB'|'PROGRAM'|'SCHEDULE'] ref [Oracle's dbms_metadata.get_ddl for object_type JOB](https://stackoverflow.com/a/37630480/2057854) | |
WHEN 'JOB' THEN 'PROCOBJ' | |
WHEN 'SCHEDULE' THEN 'PROCOBJ' | |
WHEN 'PROGRAM' THEN 'PROCOBJ' | |
ELSE pOBJECT_TYPE END; | |
--Reference: [Table 30-3 - DBMS_METADATA: Object Types](https://docs.oracle.com/cd/A97385_01/appdev.920/a96612/d_metad2.htm#1031458) | |
--Reference: [File Extension](http://www.orafaq.com/wiki/File_extensions) | |
vOBJECT_TYPE := REGEXP_REPLACE(vOBJECT_TYPE, '[[:space:]]', '_'); | |
BEGIN | |
SELECT | |
directory_name | |
INTO vDiretorio | |
FROM all_directories | |
WHERE directory_name = 'DDL_CVS_' || pOWNER | |
; | |
EXCEPTION | |
WHEN NO_DATA_FOUND THEN vDiretorio := 'DDL_SCHEMA_EXPORTED_CVS'; | |
END; | |
fname := '[' || pOWNER || ']_[' || vOBJECT_TYPE || ']_[' || pOBJECT_NAME || '].' || | |
CASE vOBJECT_TYPE | |
WHEN 'PACKAGE' THEN 'sql' | |
WHEN 'PROCEDURE' THEN 'sql' | |
ELSE 'sql' END; | |
BEGIN | |
SELECT dbms_metadata.get_ddl(vOBJECT_TYPE,pOBJECT_NAME,pOWNER) into data from dual; | |
DBMS_XSLPROCESSOR.CLOB2FILE(data,vDiretorio,fname); | |
EXCEPTION | |
WHEN OTHERS THEN | |
NULL; | |
--DBMS_OUTPUT.PUT_LINE(fname || ' ' || SQLERRM); | |
--RAISE_APPLICATION_ERROR(-20000, fname || ' ' || SQLERRM); | |
END; | |
END P_EXPORT_DDL; |
Example of procedure for generate de DDL object modified for the last day:
create or replace PROCEDURE p_export_ddl_last_modified AS
BEGIN
BEGIN
FOR cobj IN(
SELECT
*
FROM
all_objects
WHERE
owner NOT IN(
'PUBLIC',
'SYSTEM',
'SYSMAN',
'OUTLN',
'WMSYS',
'DBSNMP',
'ORACLE_OCM',
'APPQOSSYS',
'EXFSYS',
'CTXSYS',
'XDB',
'ASD',
'FLOWS_FILES',
'APEX_180100',
'SALVACAO',
'FWC',
'SVCO',
'TOCONTROL',
'MGGLO2604'
)
AND owner NOT LIKE '%BKP'
AND object_name NOT LIKE('%$%')
AND object_type NOT IN(
'LOB'
)
AND temporary NOT IN(
'Y'
)
AND status IN(
'VALID'
)
AND last_ddl_time >= trunc(SYSDATE)- 1
ORDER BY
8 DESC,
1,
2,
6
)LOOP
sys.p_export_ddl(powner => cobj.owner,pobject_type => cobj.object_type,pobject_name => cobj.object_name);
END LOOP;
END;
END p_export_ddl_last_modified;
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I made some changes to allow put in different folders for each
OWNER
Thats the final code: