with Returned Info

capturing results of sql call into variable

  1. create a new subroutine in
    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.
- Scott

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 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 and can leave alone. My ora_env file looks like this:

> more ../lib/
# 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";