2

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

Thanks

3

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.

  • No sure these approaches will work correctly (in reference to OP's question). – Raj Aug 25 '16 at 13:36
3

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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.