Re: Executing Stored Procedures and Perl DBD Oracle

From: Ron Reidy <rereidy_at_uswest.net>
Date: Sat, 13 Nov 1999 17:08:05 -0700
Message-ID: <382DFD64.7268C4C4_at_uswest.net>

You need to use the bind_param and bind_param_inout methods.  There are examples in bot the DBI, and DBD::Oracle docs (perldoc DBI, perldoc DBD::Oracle).

Jeff Thompson wrote:

I'm trying run a simple store procedure that has 1 input variable (number) and 1 output variable(varchar2).  The problem is getting output to run in PERL using the DBD::Oracle database driver.  It compiles fine and when I execute it in T.O.A.D it looks cool.  However, when I run it in PERL I get a ORA-06502 on when I try to assign a value to the output variable.

Here's the code of the stored procedure (simplified to unuseable to prove a point):

CREATE OR REPLACE PROCEDURE VALIDATE_BREEDING_2
   ( in_herd_id IN NUMBER,
     BAD_COLUMNS OUT VARCHAR2)
IS
BEGIN
IF in_herd_id > 100 THEN
   BAD_COLUMNS := 'GOOD';
ELSE
   BAD_COLUMNS := 'BAD';
END IF;
END;
/

====================================================================

Here's the PERL code:

use strict;
use DBI;
my ($dbh_oracle, $sth_oracle);
my ($sql,
    $my_output,
    $herd_id,
    $my_herd_id);
$dbh_oracle = DBI->connect('dbi:Oracle:snowball', 'nspf', 'nspf')
           || die "connect: $DBI::errstr";
           
$sql = "BEGIN
        validate_breeding_2(9999, ?); 
        END;";
$sth_oracle = $dbh_oracle->prepare($sql) || die "prep: $DBI::errstr";
$sth_oracle->execute($my_output) || die "exec: $DBI::errstr";
$my_output = $sth_oracle->fetchrow_array;
$sth_oracle->finish();
$dbh_oracle->disconnect;

print "my_output: $my_output \n";

If you could show me the correct code, I would appreciate it.
-- 
Jeff Thompson
Senior Web Developer
e-markets

 

Received on Sun Nov 14 1999 - 01:08:05 CET