I have been calling a number of Stored Procedures in an Informix DB with no problems, but now I need to run one against an Orcale DB - and I am having problems. And none of the examples I have online seem to help.

I am running the Perl from a Solaris Box, the Oracle DB is on a different Solaris Box. I have installed the Oracle Client and put up the TNSNames.ora file - I do not beleive I am having a connection problem.

The error I get is:
Can't call method "prepare" without a package or object reference at ./sp_FTD_GetNextProc.pl line 40.

The perl code is:
<code>
my $sth;
$sth = $dbh2->do("BEGIN SP_FTD_GETNEXTPROC; END;");

my $ret_value;
my $err_code;
my $err_text;
my $proc_number;
my $hbsi_hco_id;
my $hospitalid;
my $start_date;
my $stop_date;

$sth->bind_param_inout("ut_ret_value", \$ret_value, 38);
$sth->bind_param_inout("ut_err_code", \$err_code, 15);
$sth->bind_param_inout("ut_err_text", \$err_text, 255);
$sth->bind_param_inout("ut_proc_number", \$proc_number, 15);
$sth->bind_param_inout("ut_hbsi_hco_id", \$hbsi_hco_id, 15);
$sth->bind_param_inout("ut_hospitalid", \$hospitalid, 255);
$sth->bind_param_inout("ut_start_date", \$start_date, 19);
$sth->bind_param_inout("ut_stop_date", \$stop_date, 19);

$sth->execute();
</code>

I have been having a hard time finding an example. My SP does not have any input parameters and has numerous output paramters. How shoudl I be handling it? Thanks

Here is a code fragment from one of my working perl scripts. Please let me know if the answers your question.

$sth_output = $dbh_orac->prepare("BEGIN dbms_output.get_line(:line, :status); END;");
my ($line, $status) = (" ", 0);
$sth_output->bind_param_inout(":line", \$line, 100);
$sth_output->bind_param_inout(":status", \$status, 100);

while ($status == 0) {
$sth_output->execute();
print "$line\n";
}