Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Calling Oracle Stored Procedure with Perl

    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

  2. #2
    Join Date
    Oct 2003
    Location
    CT
    Posts
    1
    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";
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •