I'm using putty. How in Oracle can I export package and its body to a file in present directory using spool?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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 !!
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
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
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
You can then edit pkg.sql to your needs.