Get a PLSQL code dump of all your Oracle database code.

I used to work as a part time DBA and when I was doing my supposedely menial tasks like unlocking accounts. One of the frequent request was to get an code for the object a Function or Package or Trigger.

While this was trivial task to get an single object code it had to be quick. I never bothered to write any code or script to get that.
 However one day a developer came and asked me for a code dump for a whole schema ( We had 2500 eligible objects). What he wanted was the code dump for every plsql object stored in the system. This included triggers, functions, procedures , packages. So I searched the internet and came up with a code to do that. It was fairly small 10 liner code which was astonishing. Curiously I never saw the function used anywhere.

The function clob2file is from package dbms_xslprocessor. This one is available in oracle 11g so if you ever found yourself yearning to dump a clob object to file you should try using this. There was just a little problem I wanted to get this dump from 10G database; So I went ahead and wrote a simple script. This script is given below.

 Note : that commented line starting with dbms_xslprocessor works with oracle 11g only you should use either of the 11g or 10g call not both  so one call should always be co. You also need to modify directory names and schema names (SCOTT )


declare v_ddl_clob CLOB; procedure clob_to_file( p_dir in varchar2, p_file in varchar2, p_clob in clob ) as l_output utl_file.file_type; l_amt number default 32000; l_offset number default 1; l_length number default nvl(dbms_lob.getlength(p_clob),0); BEGIN l_output := utl_file.fopen(p_dir, p_file, 'w', 32760); while ( l_offset < l_length ) loop utl_file.put(l_output, dbms_lob.substr(p_clob,l_amt,l_offset) ); utl_file.fflush(l_output); l_offset := l_offset + l_amt; end loop; utl_file.new_line(l_output); utl_file.fclose(l_output); end; BEGIN FOR c IN ( SELECT object_name, object_type , owner FROM dba_objects WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE' , 'TRIGGER' ) and owner='SCOTT' ) -- change schema here LOOP v_ddl_clob := dbms_metadata.get_ddl(c.object_type, c.object_name , c.owner); --dbms_xslprocessor.clob2file(v_ddl_clob, '/xfer_edi/NDH/error', c.object_name||'.sql'); -- Change Dir here --dbms_advisor.create_file(v_ddl_clob, '/xfer_edi/NDH/error',c.object_name||'.sql'); clob_to_file('/xfer_edi/NDH/error',c.object_name||'.sql' , v_ddl_clob); END LOOP; END; Thanks.