[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5009
  • Last Modified:

Export Pakage or package body

Hello Experts,

I have a list of packages(BODY and SPEC ) where I want to take a backup. But I want to do it dynamically like :

List of package SPEC:
1) package1
2) package2

but I need to save it as :

1)package1_ps.sql
2)package2_ps.sql

List of package BODY that I have :
1)package4
2)package5
3)package2
4) package19

And I need to save/take backup with file name like:

1)package4_pb.sql
2)package5_pb.sql
3)package2_pb.sql
4) package19_pb.sql


Now how can I do this with a PLSQL block while spooling this to some location in c:\ drive.
And the target is to pass the package name only.

Only thing is that I want to make two blocks :
1) For package SPEC only by passing its name
2) For package BODY only by passing its name


0
Swadhin Ray
Asked:
Swadhin Ray
  • 8
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
Make individual calls to dbms_metadata.get_ddl.
0
 
sdstuberCommented:
you can only spool to one file at a time,  you could use utl_file to write the contents though

use dbms_metadata.get_ddl to extract the code

or query dba_source


or, even easier...  use Oracle's free tool  SQL Developer,  find your packages, right click,  Export DDL

you can do that for each one
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Toad, SQL Developer and dbms_metadata.get_ddl will all do... But I want to do my required work only through PLSQL ..

Can we use array type and spool one by one with required extension.

Something like :

 
declare

sql1 varchar2(1000) ;
l_packagename varchar2(200):='PACKAGE1' ;

begin
 
sql1 := 'select decode( type||''-''||to_char(line,''fm99999''),
               ''PACKAGE BODY-1'', ''/''||chr(10),
                null) ||
       decode(line,1,''create or replace '', '''' ) ||
       text text
  from user_source
 where name = upper('''||l_packagename||''')
  order by type, line ;' ;
/*
spool 'C:\backup\PACKAGE1_PB.sql' 
execute immediate sql1 ;
spool off
*/
dbms_output.put_line(sql1);

end;

Open in new window


I wanted to use spool or UTL file concept but 1st for package body...if names are only passed then everything should be done on a fly..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Spool is sqlplus only.

If you want pl/sql you need to use utl_file.

The you can write the logic/checking however you wish.
0
 
slightwv (䄆 Netminder) Commented:
I would still use dbms_metadata and select it into a CLOB.
0
 
sdstuberCommented:
try this...
ee.txt
0
 
sdstuberCommented:
note,  I used dba_source instead of dbms_metadata.get_ddl  because it made it easier to write the contents.

You can only write lines with utl_file,  I could use "put", but once I reach the line limit specified in the FOPEN,  I have to end the line.

I didn't want to take the chance of splitting the source clob contents in the middle of a key word or object name.  So, I'd either have to parse individual lines out of the clob myself, or make sure I never extracted any source more than 32K.

Iterating through dba_source lines was an easy alternative.
I just tested it on a schema of 38 packages of various size and it completed in about 1 second, so any inefficiencies seem negligible.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@slightwv can you please provide me some example ..........
0
 
slightwv (䄆 Netminder) Commented:
On mobile and cannot.  It is basically the same as sdstuber but writing the info out in chunks.
0
 
sdstuberCommented:
also note,  I didn't use execute immediate.

Using execute immediate is bad practice unless there is something actually "dynamic" in your sql,  or to execute non pl/sql commands,  like DDL.

Neither of which applies here
0
 
sdstuberCommented:
>>> but writing the info out in chunks.

it's the "chunks" that cause problems.

I just tried splitting a clob on one of my packages (mersenne twister algorithm)
 and 32767  just happened to be in the middle of a string concatenation "||'

Had I written  
|
|

instead of ||

the code in the file would no longe compile.  
The solution to that is to parse the clob, but iterating through the source lines is easier to do with the existing views
0
 
slightwv (䄆 Netminder) Commented:
If you use put and not put-line it should retain the cr/lf in the output.  I think it should?
0
 
sdstuberCommented:
no,  as noted above, when you reach the line limit specified in FOPEN,  you must end the line.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@sdstuber:  

I have created a directory in my DB and given the grants for read and write to use 'XX'

My Oracle server is in Unix but I want to pull the file in Windows machine.

I created the directory by giving the path as 'c:\temp' ... will this help ..


And also in your code where I can pass the list of the file name ??

0
 
slightwv (䄆 Netminder) Commented:
>>My Oracle server is in Unix but I want to pull the file in Windows machine

UTL_FILE can only write to a filesystem seen by the database server.  It cannot write to a remote machine.

Still not sure why you insist on PL/SQL for this when sqlplus spool commands should do the same thing.
0
 
sdstuberCommented:
>> And also in your code where I can pass the list of the file name ??

The file name is constructed in this part of the code...
Change this to be whatever you want, I'm naming it by what you have above  "packagename_ps.sql" or "packagename_pb.sql"


  IF x.TYPE = 'PACKAGE'
            THEN
                v_file  := UTL_FILE.fopen('TMP_DIR', x.name || '_ps.sql', 'w', 32767);
            ELSE
                v_file  := UTL_FILE.fopen('TMP_DIR', x.name || '_pb.sql', 'w', 32767);
            END IF;
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@sdstuber: I am telling if I have to pass only 5 package then how to pass it ...

 
0
 
sdstuberCommented:
FOR x IN (SELECT   owner, name, TYPE, line, text
                  FROM dba_source
                 WHERE type IN ('PACKAGE', 'PACKAGE BODY')
                 AND owner = USER   --- change this whatever owner you want
AND name in ('PACKAGE1','PACKAGE2','PACKAGE3','PACKAGE4','PACKAGE5')
              ORDER BY owner, name, TYPE, line, text)
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now