Swadhin Ray
asked on
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
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
Make individual calls to dbms_metadata.get_ddl.
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
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
ASKER
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 :
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..
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;
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..
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.
If you want pl/sql you need to use utl_file.
The you can write the logic/checking however you wish.
I would still use dbms_metadata and select it into a CLOB.
try this...
ee.txt
ee.txt
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.
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.
ASKER
@slightwv can you please provide me some example ..........
On mobile and cannot. It is basically the same as sdstuber but writing the info out in chunks.
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
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
>>> 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
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
If you use put and not put-line it should retain the cr/lf in the output. I think it should?
no, as noted above, when you reach the line limit specified in FOPEN, you must end the line.
ASKER
@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 ??
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 ??
>>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.
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.
>> 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;
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;
ASKER
@sdstuber: I am telling if I have to pass only 5 package then how to pass it ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks