Here is part of the code that i have previously used to execute 
SQL scripts. I have just copied part of it here so dont try to 
run it as it is. But it should give u a good idea. 

use strict;
use DBI;
use IPC::Open3;
use IO::Select;
use POSIX ":sys_wait_h";

my $sqlplus = $ENV{ORACLE_HOME}.'/bin/sqlplus';
my $job_file='xyz.sql'; #Sql Script to execute
my @par_list=('abc','4','i'); #list of parameters to be passed 
to the script
my $connect='user@db'; #Database to connect to
my $password; #password for the user

  if ( -e $job_file ){    # Check if file exists
    if ( -r $job_file ) { # Check if file is readable
      $cmd="$sqlplus '$connect' \@$job_file @par_list";

      eval{
        $pid = open3(*CMD_IN2, *CMD_OUT2, *CMD_ERR2, $cmd);
      };
      if ($@){
        job_sqlscript_handler->error_handler("Unable to startup 
up sql plus".
                                            "due to OS error 
$@");
        return 1;
      }

      eval {
        print CMD_IN2 "$passwd \n";
        close (CMD_IN2);
        $selector=IO::Select->new;
        $selector->add(*CMD_ERR2, *CMD_OUT2);
        while (@ready= $selector->can_read){
          foreach $gh (@ready){
            $stdout .= scalar <$gh>;
            if ((length($stdout) > 10000)) {
              print $LF "\n$stdout\n";
              $stdout = '';
            }
            $selector->remove($gh) if eof($gh);
          }
        }
        close(CMD_OUT2);
        close(CMD_ERR2);
      };
      if ($@){
        print $LF "\n$stdout\n" if $stdout;
        job_sqlscript_handler->error_handler("Error in the eval 
during IO handling of $cmd in sql script");
        return 1;
      }

      waitpid($pid,0); 
      if ($? !=0){
        print $LF "\n$stdout\n" if $stdout;
        job_sqlscript_handler->error_handler("Error returned in 
executing ".
                                             "$cmd in sql 
script");
        $chld_status=1;
      }
      else {
        print $LF "\n$stdout\n" if $stdout;
        $chld_status=0;
      }
      
    }
    else {
      print $LF "SQL File $job_file is NOT READABLE\n"; 
      return 1;
    }
  }
  else {
    print $LF "SQL File $job_file DOES NOT EXISTS\n"; 
    return 1;
  }


________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag

Reply via email to