Re: How to export a package and a procedure

Nilesh,
You can use DBMS_METADATA to extract the code for stored procedures/packages/functions. It is available in 9i and 10g, though expect bugs. Nothing that crashes a database, but it might not output the code exactly the way you want it. Test and make slight changes if you need. The code below is a subset of a script I use to extract all the ddl from a schema. Use with care and change what you need.
SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000 
COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); 
COLUMN ddl_string FORMAT A4000

PROMPT
PROMPT -- Schema Functions
PROMPT

SELECT DBMS_METADATA.GET_DDL('FUNCTION', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
 AND o.type# = 8
/

PROMPT
PROMPT -- Schema Packages (specs and body)
PROMPT

SELECT DBMS_METADATA.GET_DDL('PACKAGE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
 AND o.type# = 9
/

PROMPT
PROMPT -- Schema Procedures
PROMPT
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', o.name,'&&schema_owner') ddl_string 
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
 AND o.type# = 7
/


Daniel Fink


--
Daniel Fink

Oracle Performance, Diagnosis and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com


nilesh kumar wrote:

Hello All,
I have an issue i need to export a package and a procedure too which is present in another package , is there any solution for this in datapump or exp utility.Or is there any other solution for this to do pls help , i am using 9i and 10g both . 

Thanks
Nilesh soni


--
http://www.freelists.org/webpage/oracle-l

Other related posts: