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