RE: How to export a package and a procedure

Getting DBMS_METADATA to produce code just the way you want it may require using more than the "browse" (GET_DDL) interface. There's a very rich set of functionality that can be accessed only by using the full API. If you're interested in more information, you can take a look at a presentation I did on this last year, at

http://www.nyoug.org/Presentations/2006/September_NYC_Metro_Meeting/200609Baumgartel_Introduction%20to%20DBMS_METADATA.pdf

Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing Databases Americas One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel W. Fink Sent: Friday, July 13, 2007 5:54 PM
To: nileshkum_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: 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



==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

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