ospinala
asked on
Perl won't end when calling an Oracle Stored Procedure
I am using Perl to access a database by calling an Oracle Stored Procedure, and it is working, accessing the data fine, but when the program is complete, it doesn't end the process on the server. Has anyone run into this problem before? Below is my code, including the point from which the program will not terminate. Any help you could provide would be appreciated.
#!perl
use DBI;
use DBD::Oracle qw(:ora_types);
#DBI->trace(1);
my $dbh = DBI->connect( 'dbi:Oracle:rptprod', 'username', 'password', {RaiseError=>1} ) or die;
my $employees_cursor;
my $sql = "BEGIN " . "cb_provider.detroit.teste r1(:r, :proj_id, :fiscal_period, :fiscal_year, :status); " . "END;";
my @row;
my $sth = $dbh->prepare($sql);
$sth->bind_param( ":proj_id", "400242.200" );
$sth->bind_param( ":fiscal_period", 11 );
$sth->bind_param( ":fiscal_year", "2003" );
$sth->bind_param( ":status", "" );
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET });
#### IF I EXIT BEFORE THIS EXECUTE STATEMENT, PERL ENDS GRACEFULLY
$sth->execute ();
#### IF I EXIT AFTER THIS EXECUTE STATEMENT, PERL CONTINUES RUNNING UNTIL I CLOSE DOS WINDOW (IT DOES GET TO THE LAST LINE OF CODE (IT PRINT 'END')).
my $count = 0;
while (( @row = $data->fetchrow_array() )&&($count < 99)) {
$count++;
print "$count - ";
foreach $_ (@row) {
print $_ if defined $_;
print "\t";
}
print "\n";
}
$data->finish ();
$sth->finish ();
$dbh->disconnect ();
print "END\n";
EXIT;
#!perl
use DBI;
use DBD::Oracle qw(:ora_types);
#DBI->trace(1);
my $dbh = DBI->connect( 'dbi:Oracle:rptprod', 'username', 'password', {RaiseError=>1} ) or die;
my $employees_cursor;
my $sql = "BEGIN " . "cb_provider.detroit.teste
my @row;
my $sth = $dbh->prepare($sql);
$sth->bind_param( ":proj_id", "400242.200" );
$sth->bind_param( ":fiscal_period", 11 );
$sth->bind_param( ":fiscal_year", "2003" );
$sth->bind_param( ":status", "" );
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET });
#### IF I EXIT BEFORE THIS EXECUTE STATEMENT, PERL ENDS GRACEFULLY
$sth->execute ();
#### IF I EXIT AFTER THIS EXECUTE STATEMENT, PERL CONTINUES RUNNING UNTIL I CLOSE DOS WINDOW (IT DOES GET TO THE LAST LINE OF CODE (IT PRINT 'END')).
my $count = 0;
while (( @row = $data->fetchrow_array() )&&($count < 99)) {
$count++;
print "$count - ";
foreach $_ (@row) {
print $_ if defined $_;
print "\t";
}
print "\n";
}
$data->finish ();
$sth->finish ();
$dbh->disconnect ();
print "END\n";
EXIT;
also to debug more
try to tweak your code like this.
$sth->bind_param( ":proj_id", "400242.200" ) or warn "DB error, cannot bind proj: ".$DBI::errstr;
$sth->bind_param( ":fiscal_period", 11 ) or warn "DB error, cannot bind fiscal period: ".$DBI::errstr;
$sth->bind_param( ":fiscal_year", "2003" ) or warn "DB error, cannot bind fiscal_year: ".$DBI::errstr;
$sth->bind_param( ":status", "" );
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET }) or warn "DB error, cannot bind data: ".$DBI::errstr;
u might find where is your problem.
try to tweak your code like this.
$sth->bind_param( ":proj_id", "400242.200" ) or warn "DB error, cannot bind proj: ".$DBI::errstr;
$sth->bind_param( ":fiscal_period", 11 ) or warn "DB error, cannot bind fiscal period: ".$DBI::errstr;
$sth->bind_param( ":fiscal_year", "2003" ) or warn "DB error, cannot bind fiscal_year: ".$DBI::errstr;
$sth->bind_param( ":status", "" );
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET }) or warn "DB error, cannot bind data: ".$DBI::errstr;
u might find where is your problem.
ASKER
shivsa,
I tried both your suggestions to no avail. Keep in mind that the program runs to the end without incident. It displays the data that is pulled and continues to the end when it displays END as you would expect from the code. Then it just hangs and the process continues on the server. I have to hit CTRL-C to terminate the process. The perl code will terminate fine with an exit statement before the execute line, but if I try to exit at any point after that line, perl will not terminate.
I tried both your suggestions to no avail. Keep in mind that the program runs to the end without incident. It displays the data that is pulled and continues to the end when it displays END as you would expect from the code. Then it just hangs and the process continues on the server. I have to hit CTRL-C to terminate the process. The perl code will terminate fine with an exit statement before the execute line, but if I try to exit at any point after that line, perl will not terminate.
did u get any error as i provided the debug messages there.
i understand it goes till end but why it is hanging to debug that we might need some more info that where it is hanging, is it code problem, data binding problem so that when execute happens it happens in loop and never come back and all while the perl code keep running.
i understand it goes till end but why it is hanging to debug that we might need some more info that where it is hanging, is it code problem, data binding problem so that when execute happens it happens in loop and never come back and all while the perl code keep running.
ASKER
Got no errors. Here is the exact output:
D:\DATA>perl oracon.pl
1 - 400242.200 11 2003 1.80.40 INACTIVE 1068.85 1068.85 0
0 0 1068.85 778 0
END
The cursor continues to blink on the line below END. The only way to stop it is CTRL-C, which is shown below:
D:\DATA>perl oracon.pl
1 - 400242.200 11 2003 1.80.40 INACTIVE 1068.85 1068.85 0
0 0 1068.85 778 0
END
Terminating on signal SIGINT(2)
D:\DATA>
D:\DATA>perl oracon.pl
1 - 400242.200 11 2003 1.80.40 INACTIVE 1068.85 1068.85 0
0 0 1068.85 778 0
END
The cursor continues to blink on the line below END. The only way to stop it is CTRL-C, which is shown below:
D:\DATA>perl oracon.pl
1 - 400242.200 11 2003 1.80.40 INACTIVE 1068.85 1068.85 0
0 0 1068.85 778 0
END
Terminating on signal SIGINT(2)
D:\DATA>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
exec "echo END"; worked in ending the process.
Also undef $data; worked (undef $sth and undef $dbh were unnecessary).
I've added undef $data; to my program right after $data->finish (); and the program works fine.
Can you see anything in the code that I can change to avoid having to add undef $data, or why it is getting hung up?
Otherwise, I will be accepting your answer.
Also undef $data; worked (undef $sth and undef $dbh were unnecessary).
I've added undef $data; to my program right after $data->finish (); and the program works fine.
Can you see anything in the code that I can change to avoid having to add undef $data, or why it is getting hung up?
Otherwise, I will be accepting your answer.
theory says that $data->finish () should close the cursor u opened with $sth->bind_param_inout.
may be the sequence is not right or something like u could try one more suggestion i do not know if it works.
try closing $sth cursor first and then $data.
also what might wanna update your Oracle too since there were some bug fixes related to this problem.
may be the sequence is not right or something like u could try one more suggestion i do not know if it works.
try closing $sth cursor first and then $data.
also what might wanna update your Oracle too since there were some bug fixes related to this problem.
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET });
to
$sth->bind_param_inout( ":data", \$data, 0, { ora_type => ORA_RSET });