Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Perl in SQL Plus Script Generation Helps DBA's
Hi !
I'm starting to do some dba stuff.
I needed to copy a list of procedures/functions from development DB to other
DB's.
So I came up with a run control file like this: <<<< Begin File: cp_obj.rc >>>>
#---------------------------------------------------- # This shows how easy is to update a list of objects # from development DB into test and production DB's #
#----------------------------------------------------
# Export objects is object_list file from Development
SQL connect devel/devel_at_develdb;
EXPORT_SOURCE object_list
# Import into Test DB
SQL connect test/test_at_testdb;
IMPORT_SOURCE object_list
SQL @compile_invalid;
# Import into Production
SQL connect prod/prod_at_proddb;
IMPORT_SOURCE object_list
SQL @compile_invalid;
<<<< End File: cp_obj.rc >>>>
And now I just run the command: "dba cp_obj.rc"
where dba.bat is
del cmds
copy %1 cmds
perl dba.pl > sc.sql
plus80 -s @sc.sql > log
See dba.pl (Perl Script) and Help file at the end.
So what is happening ?
I write a run control file, pass it to a Perl script and it generates a SQL
Plus script(sc.sql). This is sent to SQL Plus in dba.bat to run the
commands.
For the IMPORT and EXPORT source commands you need to create directory
"source".
Many other commands can be created. I think SQL Plus script generation can become very usefull. A well written Perl script can be a great tool.
If you would like to do more or have any suggestions, please contact me.
Rui Anastacio
<<<<Begin File: dba.pl>>>>
print<<EOF;
set pages 0;
set feedback off;
EOF open CMD,"<cmds";
while (<CMD>) {
chop; uc;
if (/^#.*/ || /^$/) { next; }
m/(^[A-Z_]+)/; $cmd = $1;
if (/\s(.*$)/) { $par = $1; } else { $par = ''; }
SWITCH: {
if (/^EXPORT_SOURCE/) { &Export_Source; last; } if (/^IMPORT_SOURCE/) { &Import_Source; last; } if (/^SQL/) { &Sql; last; }
print<<EOF;
exit;
EOF
#-----------------------------------------------------------
sub Export_SourceSQL {
print<<EOF;
spool source\\$_.sql;
select 'CREATE OR REPLACE' from dual;
SELECT text
FROM USER_SOURCE
WHERE name = upper('$_')
ORDER BY line;
spool off;
EOF
}
sub Export_Source {
if ($par) {
open IN,"<$par";
while (<IN>) {
chop; uc; &Export_SourceSQL;
chop; uc; if ($_ eq '.') { last; } &Export_SourceSQL;
sub Import_Source {
if ($par) {
open IN,"<$par";
while (<IN>) {
chop; uc; print "start source\\$_.sql\n/\n";}
chop; uc; if ($_ eq '.') { last; } print "start source\\$_.sql\n/\n";}
sub Sql {
if ($par) {
print $par,"\n";
}
else {
while (<CMD>) {
chop; uc;
if ($_ eq '.') { last; }
print $_,"\n";
}
}
}
<<<< End File: dba.pl >>>>
FILE: dba.txt
<<<< Begin File: dba.txt >>>>
DBA Help
Command File ---> DBA ---> SQL PLUS Script ---> SQL PLUS cmds
CMDS Format
Blank lines are omitted. Lines starting with # are omitted.
---
SQL
---
Sintaxe
SQL
commands
.
or
SQL commands
Description
Writes the commands to the script.
Examples
SQL connect test/123_at_develop;
SQL
spool clients
select * from clients;
spool off;
.
or
EXPORT_SOURCE
object1
objectN
.
Description
Gets the source from objects and writes to source directory. Each
object source is read from USER_SOURCE and is written a a file with
the name: object.sql
In the first form it will read the list of objects from a file
Examples
EXPORT_SOURCE
my_func
my_proc
.
EXPORT_SOURCE alter_objects
or
IMPORT_SOURCE
object1
objectN
.
Description
Reads each of the object files from source directory and start's them.
Example
#---------------------------------------------------- # This shows how easy is to update a list of objects # from development DB into test and production DB's #
#----------------------------------------------------
# Export objects is object_list file from Development
SQL connect devel/devel_at_develdb;
EXPORT_SOURCE object_list
# Import into Test DB
SQL connect test/test_at_testdb;
IMPORT_SOURCE object_list
SQL start compile_invalid;
# Import into Production
SQL connect prod/prod_at_proddb;
IMPORT_SOURCE object_list
SQL start compile_invalid;
<<<< End File: dba.txt >>>> Received on Mon Dec 06 1999 - 05:05:44 CST