OraFAQ Forum: SQL & PL/SQL » How to Export Package Body using Spool Command

Home » SQL & PL/SQL » SQL & PL/SQL » How to Export Package Body using Spool Command (Oracle 9i, SQLPLUS) (

) 1 Vote

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

How to Export Package Body using Spool Command [message #574219]

Tue, 08 January 2013 02:40

bsm.java
Messages: 5
Registered: January 2013
Location: Singapore

Junior Member

Hi,

I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:

WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;

SPOOL c:\temp\PKG_PP_Body_BKP_08Jan13.TXT;
select text from all_source where owner = 'owner' and name = 'SPI_xxxx_PCK' and TYPE = 'PACKAGE BODY' order by line;
SPOOL OFF;
SET ECHO ON;
SET FEEDBACK ON

WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......

In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only. Anyone knows how to achive this?

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574221 is a reply to message #574219]

Tue, 08 January 2013 02:51

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

I guess, SET LONG will solve your problem.

regards,
Delna

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574222 is a reply to message #574221]

Tue, 08 January 2013 02:53

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Can you check the length of the text..

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574223 is a reply to message #574219]

Tue, 08 January 2013 02:55

flyboy
Messages: 1673
Registered: November 2006

Senior Member

WAY1: Different from what? Just missing CREATE (OR REPLACE) keywords? Or is the database code wrapped? Or ...?

WAY2: set long 4000

WAY3: The first way points to the fact that there is another code repository. Keep it as the main one. Do not use database as your content version system (CVS) - it is not supposed to act so. Then, there will be no need to extract code from it anymore.

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574233 is a reply to message #574219]

Tue, 08 January 2013 03:41

Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The best way is to use dbms_metadata.get_ddl function.

Regards
Michel

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574302 is a reply to message #574233]

Tue, 08 January 2013 20:00

bsm.java
Messages: 5
Registered: January 2013
Location: Singapore

Junior Member

Thanks to everyone. Your suggestions triggers me to get some solution. Finally I resolved my issue as follows:

SET HEAD OFF
SET ECHO OFF
SET FEED OFF
SET TERM OFF
SET LINE 1500
SET NEWPAGE NONE
set pagesize 0
SPOOL C:\temp\SPI_xxx_PSPEC.SQL
PROMPT CREATE OR REPLACE
select trim(text) from user_source
where name='SPI_xxx_PCK' AND type='PACKAGE';
SPOOL OFF

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574325 is a reply to message #574302]

Wed, 09 January 2013 01:14

Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select dbms_metadata.get_ddl('PACKAGE','SPI_xxx_PCK',USER) from dual;

Regards
Michel

[Updated on: Wed, 09 January 2013 01:15]

Report message to a moderator

Re: How to Export Package Body using Spool Command [message #574388 is a reply to message #574325]

Wed, 09 January 2013 21:07

bsm.java
Messages: 5
Registered: January 2013
Location: Singapore

Junior Member

Thanks Michel. I found the above way is also useful.

Report message to a moderator

Previous Topic:	query fine tune
Next Topic:	Extract date from string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 20:52:48 CDT 2013

Total time taken to generate the page: 0.10486 seconds