Message-ID: <4697F477.3060704@optimaldba.com>
Date: Fri, 13 Jul 2007 15:53:59 -0600
From: "Daniel W. Fink" <daniel.fink@optimaldba.com>
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
MIME-Version: 1.0
To:  nileshkum@gmail.com
CC:  oracle-l@freelists.org
Subject: Re: How to export a package and a procedure
References: <7877ab4b0707131426i59f1897bo41011d82c7e37f11@mail.gmail.com>
In-Reply-To: <7877ab4b0707131426i59f1897bo41011d82c7e37f11@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: daniel.fink@optimaldba.com

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