Oracle export DDLS into zip

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';

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;
/

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;
/

Execute function

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

select get_source from dual

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