oracle.pl with Returned Info

capturing results of sql call into variable

1. create a new subroutine in oracle.pl....

   sub sql_results {
   local ($sql_login, @sql_code) = @_;
   $sql_code_begin = "set echo off pages 200 verify off head off feedback
   off lines 500 ARRAYSIZE 1 escape ^\n";
   $sql_code_end = "\nset head on lines 80 pages 23 feedback on\nquit;\n";
   @sql_array[0..2] = ($sql_code_begin, @sql_code, $sql_code_end);
   @sql_code = @sql_array;
   $random = time();
   $rand = substr($random,length($random)-8,8);
   while ( -e "$tmpdir/$rand.sql" ) { $rand = $rand - 600; }
   $sql_file = $tmpdir . "/" . $rand . ".sql";
   open (SQLCODE, ">$sql_file");
   print SQLCODE @sql_code;
   close (SQLCODE);
   $results = `$sqlplus $sql_login \@$sql_file`;
   unlink ("$sql_file");
   return $results;
   }
   

2. issue the call to this routine in your perl program....

   $results = sql_results($sql_login,@sql_code);

3. you can now manipulate $results however you want.

ps. You may notice that there's a $tmpdir in the above call. this variable is also in my regular &sql() subroutine. I created a separate file called ora_env.pl that gets required in my oracle-perl scripts. This library just defines Oracle environment so that if I ever have to move these things onto another machine, I just need to change ora_env.pl and can leave oracle.pl alone. My ora_env file looks like this:

> more ../lib/ora_env.pl
# Local environment variable initialization
# These values may change from system to system and from user to user.
# (LD_LIBRARY_PATH is needed on some servers but not on others)

$ENV{'ORACLE_SID'} = "main";
$ENV{'ORACLE_HOME'} = "/u01/app/oracle/product/8.0.5";
#$ENV{'LD_LIBRARY_PATH'} = "/u01/oracle/product/732/lib";

$sqlplus = "/u01/app/oracle/product/8.0.5/bin/sqlplus -S";
$tmpdir = "/var/tmp";


1;