Export Packages, Procedures, Functions to individual files from Oracle at Client Side

With this post I am simply sharing a SQL script, which I developed to export all Packages, Procedures, Functions and others from Oracle Database to individual files at client machine.

To execute this follow these simple steps:
1. save this script in directory with name "GenerateExportScript.sql"
2. open a command prompt or shell
3. go to that directory
4. connect to your Oracle Schema from SQLPlus
5. call this script like @GenerateExportScript.sql

Wait for some time while your code is being exported, and individual files for each Package, Package Body, Function, Procedure, Trigger and other objects are being generated at your client machine.

Here is the script:

-- -----------------------------------------------------------------------------------
-- File Name    : GenerateExportScript.sql
-- Author       : Nimish Garg
-- Description  : Export all code in the schema and generate individual files
-- Call Syntax  : @GenerateExportScript
-- Last Modified: 15/09/2017
-- -----------------------------------------------------------------------------------

set serveroutput on
set termout off 
set heading off 
set feedback off 
set linesize 50 
spool ExportCode.sql 

declare
 l_ext varchar2(100);
begin
for c in (select distinct name, type from user_source order by 1)
loop

 if c.type = 'PROCEDURE' then l_ext := '.prc';
 elsif c.type = 'PACKAGE' then l_ext := '.pks';
 elsif c.type = 'PACKAGE BODY' then l_ext := '.pkb';
 elsif c.type = 'TRIGGER' then l_ext := '.trg';
 elsif c.type = 'FUNCTION' then l_ext := '.fnc';
 else l_ext := '.sql';
 end if;

 dbms_output.put_line('set feedback off');
 dbms_output.put_line('set heading off');
 dbms_output.put_line('set termout off'); 
 dbms_output.put_line('set linesize 1000');
 dbms_output.put_line('set trimspool on'); 
 dbms_output.put_line('set verify off');
 dbms_output.put_line('spool ' || c.name || l_ext);
 
 dbms_output.put_line('prompt set define off ');
 dbms_output.put_line('select decode(line,1,''create or replace '', '''' ) || text text from user_source where name = ''' || c.name || ''' and type = ''' || c.type || ''' order by type, line;' );
 dbms_output.put_line('prompt /');
 dbms_output.put_line('prompt set define on');
 
 dbms_output.put_line('spool off');
 dbms_output.put_line('set feedback on ');
 dbms_output.put_line('set heading on ');
 dbms_output.put_line('set termout on ');
 dbms_output.put_line('set linesize 100 ');
 dbms_output.put_line(chr(13) || chr(10));
 dbms_output.put_line(chr(13) || chr(10));
end loop;
end;
/ 

spool off


@@ExportCode.sql

The Great Tom Kyte has also written a SQL script for this purpose, you might want to have a look on that too.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:743825617527

Feel free to modify and distribute it, hopefully by my name :) Have Fun !

Related Posts:
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle Database 12.1 New Features for Developers
- How does Oracle Client connect with Oracle Server
- How to connect sqlplus without tnsnames.ora
- How to Get Execution Plan and Statistics of SQL Query