Perl in SQL Plus Script Generation Helps DBA's

From: Rui Anastácio <coreto03_at_axa-seguros.pt>
Date: Mon, 6 Dec 1999 11:05:44 -0000
Message-ID: <82g67t$29u$1@duke.telepac.pt>

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
#

# compile_invalid is a script that does that

#----------------------------------------------------

# 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; }

}

}
close CMD;

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;

   }
   close IN;
}

 else {
   while (<CMD>) {

     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";

   }
   close IN;
}

 else {
   while (<CMD>) {

     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

---

 Perl SQL PLUS script generation
 Rui Anastacio

 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;
   .

---

 EXPORT_SOURCE

---

 Sintaxe
   EXPORT_SOURCE object_list_file

   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

---

 IMPORT_SOURCE

---

 Sintaxe
   IMPORT_SOURCE object_list_file

   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
   #

   # compile_invalid is a script that does that

   #----------------------------------------------------

   # 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