Oracle export DDLS into zip
Tags: oracle, orclapex, plsql
Published: 2018-03-16 11:25:35

---
title: Oracle export DDLS into zip
published: true
description: 
tags: oracle, orclapex, pl/sql
---


##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

```sql
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

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

```sql
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.  

```sql
select get_source from dual
```

Now I can save DDL source from SQL developer to my PC (git/svn repo). :)
![](https://thepracticaldev.s3.amazonaws.com/i/6lx50abvfw30we4ocm32.png)