DEV Community

isabolic99
isabolic99

Posted on • Edited on

Oracle export DDLS into zip

#oracle #orclapex #plsql

The goal

The goal was to create a single PL-SQL function that will generate a zip file with all database objects source(DDL).

Find the necessary objects with the SQL query 

SELECT lower(object_name) || decode(object_type, 'VIEW', '.vw', 'TRIGGER', '.trg', 'PACKAGE', '.pks', 'PACKAGE BODY', '.pkb', '.sql') file_name, dbms_metadata.get_ddl(object_type,object_name, owner) file_content FROM all_objects WHERE owner = 'DEMO';
Enter fullscreen mode Exit fullscreen mode

The query will return two columns file_name with extension and file_content, for generating file_content I have used Oracle build in function "dbms_metadata.get_ddl" a function that returns DDLs (source of objects) CLOB.
Now that query is ready I need to create a function that will return zip with all DDLs in files.

Functions clob_to_blob and get_source

Since a didn't find any build-in function for clob to blob a had to create my own, once again StackOverflow was very useful:) The second function get_source consists of:

  • running FOR LOOP on a query from above
  • call clob_to_blob (convert ddl content to blob)
  • adding blob file output to zip file, this done with apex_zip package from oracle
  • once FOR LOOP is done finish zip and RETURN zip BLOB 
CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB) RETURN BLOB AS v_blob BLOB; v_varchar RAW (32767); v_start BINARY_INTEGER := 1; v_buffer BINARY_INTEGER := 32767; BEGIN dbms_lob.createtemporary (v_blob, false); FOR i in 1 .. ceil (dbms_lob.getlength (clob_in) / v_buffer) LOOP v_varchar := utl_raw.cast_to_raw (dbms_lob.substr (clob_in, v_buffer, v_start)); dbms_lob.append (v_blob, v_varchar); v_start := v_start + v_buffer; END LOOP; RETURN v_blob; END clob_to_blob; /
Enter fullscreen mode Exit fullscreen mode 
CREATE OR REPLACE FUNCTION get_source RETURN BLOB AS l_zip_file BLOB; v_file BLOB; BEGIN FOR l_file IN ( SELECT lower(object_name) || decode(object_type, 'VIEW', '.vw', 'TRIGGER', '.trg', 'PACKAGE', '.pks', 'PACKAGE BODY', '.pkb', '.sql') file_name, dbms_metadata.get_ddl(object_type,object_name, owner) file_content FROM all_objects WHERE owner = 'DEMO' ) LOOP --call clob to blob fn v_file := clob_to_blob(l_file.file_content); --add file into zip apex_zip.add_file( p_zipped_blob => l_zip_file ,p_file_name => l_file.file_name ,p_content => v_file ); END LOOP; -- finish zip apex_zip.finish(p_zipped_blob => l_zip_file); RETURN l_zip_file; END; /
Enter fullscreen mode Exit fullscreen mode

Execute function

Once everything is compiled on DB I can call the function get_source from SQL or PLSQL.

select get_source from dual
Enter fullscreen mode Exit fullscreen mode

Now I can save DDL source from SQL developer to my PC (git/svn repo). :)

Top comments (0)