How in Oracle can I export package that will have package and body

oracleplsqlsqlplus

I'm using putty. How in Oracle can I export package and its body to a file in present directory using spool?

Thanks

Best Answer

There are two ways to achieve this

Way 1 :

SET HEAD OFF
SET ECHO OFF
SET FEED OFF
SET TERM OFF
SET LINE 1500
SET NEWPAGE NONE
set pagesize 0
SPOOL C:\temp\FILE_NAME.SQL
PROMPT CREATE OR REPLACE
select trim(text) from user_source
where name='PACKAGE_NAME' AND type='PACKAGE';
SPOOL OFF

Way 2:

select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME',USER) from dual;

Another way suggested by @vercelli that is exporting the package and it's body using Oracle expdp utility. A wonderfull method :) 

expdp scott/tiger@db10g schemas=SCOTT INCLUDE=PACKAGE LIKE 'PACKAGE_NAME' SQLFILE=scott.sql directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Hope this helps !!
Regards.

Related Question