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