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). :)
Top comments (0)