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

Question

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

Thanks

Answer 1

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.

Answer 2

Use SYS.DBMS_METADATA.GET_DDL to get a CLOB with the code:

declare 
  myPackageSpec clob;
  myPackageBody clob;
BEGIN
    select dbms_metadata.get_ddl('PACKAGE_BODY', 'PACKAGE_NAME', 'SCHEMA') 
       into myPackageBody
      FROM DUAL;
    select dbms_metadata.get_ddl('PACKAGE_SPEC', 'PACKAGE_NAME', 'SCHEMA') 
      into myPackageSpec 
    FROM DUAL;

  dbms_output.put_line(myPackageSpec);
  dbms_output.put_line(myPackageBody);
end;

You could also use expdp from command line and then impdpwith sqlfile

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

Answer 3

if you want to use spool in sqlplus following template can be useful,

set line xxx -- whatever makes sense
set pagesize 0 -- no headers
set feedback off
spool my_pkg.sql
select text from user_source where name = 'MY_PKG' order by type, line;
end spool

expdp (internally uses dbms_metadata), or metadata based approaches will work, however in the approach provided by expdp, you must

1. run expdp with INCLUDE=PACKAGE option.
2. run impdp using same dumpfile you created in previous step and use SQLFILE=my_pkg.sql option to generate sql code.

You can then edit pkg.sql to your needs.