Home » SQL & PL/SQL » SQL & PL/SQL » How to Export Package Body using Spool Command (Oracle 9i, SQLPLUS)  () 1 Vote
icon5.gif  How to Export Package Body using Spool Command [message #574219] Tue, 08 January 2013 02:40 Go to next message
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?
Re: How to Export Package Body using Spool Command [message #574221 is a reply to message #574219] Tue, 08 January 2013 02:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I guess, SET LONG will solve your problem.

regards,
Delna
Re: How to Export Package Body using Spool Command [message #574222 is a reply to message #574221] Tue, 08 January 2013 02:53 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you check the length of the text..
Re: How to Export Package Body using Spool Command [message #574223 is a reply to message #574219] Tue, 08 January 2013 02:55 Go to previous messageGo to next message
flyboy
Messages: 1770
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.
Re: How to Export Package Body using Spool Command [message #574233 is a reply to message #574219] Tue, 08 January 2013 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best way is to use dbms_metadata.get_ddl function.

Regards
Michel
Re: How to Export Package Body using Spool Command [message #574302 is a reply to message #574233] Tue, 08 January 2013 20:00 Go to previous messageGo to next message
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
Re: How to Export Package Body using Spool Command [message #574325 is a reply to message #574302] Wed, 09 January 2013 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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 Go to previous message
bsm.java
Messages: 5
Registered: January 2013
Location: Singapore
Junior Member

Thanks Michel. I found the above way is also useful.
Previous Topic: query fine tune
Next Topic: Extract date from string
Goto Forum:
  


Current Time: Sat Aug 30 11:56:32 CDT 2014

Total time taken to generate the page: 0.12893 seconds