Solved

Perl won't end when calling an Oracle Stored Procedure

Posted on 2003-12-09
8
1,168 Views
Last Modified: 2012-06-27
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.tester1(: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;
0
Comment
Question by:ospinala
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:shivsa
ID: 9905763
change this line to
$sth->bind_param_inout( ":r", \$data, 0, { ora_type => ORA_RSET });
to
$sth->bind_param_inout( ":data", \$data, 0, { ora_type => ORA_RSET });
0
 
LVL 24

Expert Comment

by:shivsa
ID: 9905785
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.
0
 
LVL 1

Author Comment

by:ospinala
ID: 9906009
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 24

Expert Comment

by:shivsa
ID: 9906046
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.

0
 
LVL 1

Author Comment

by:ospinala
ID: 9906145
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>
0
 
LVL 3

Accepted Solution

by:
terageek earned 500 total points
ID: 9907170
Can you check if there are any child processes haning around that perl might be waiting for?

When you call exit in perl, perl will try to do some cleanup before actually exiting by executing all END blocks and all DESTROY functions for any existing objects.

You can see if perl is getting hung up on any destructors by trying to undefine each of your objects before exiting.  I doubt this is the case since you do seem to be doing the cleanup nicely, but you can try this...

print "Destroying data\n"
undef $data;
print "Destroying sth\n";
undef $sth;
print "Destorying dbh\n";
undef $dbh;

You can avoid that cleanup using POSIX::_exit(), which will immediately exit without calling any END blocks or DESTROY subroutines...

use POSIX;
&POSIX::_exit(0);

You must pass POSIX::_exit an error status for it to work.  If POSIX isn't available, you can call exec which will also immediately terminate the perl program without calling any END blocks or DESTROY functions.

exec "echo END";

0
 
LVL 1

Author Comment

by:ospinala
ID: 9907940
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.
0
 
LVL 24

Expert Comment

by:shivsa
ID: 9908215
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.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sending email via Perl on Windows 3 163
perl search and replace 6 168
perl split by | 2 97
Replace  text in a file 2 105
I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question