Executing Stored Procedures and Perl DBD Oracle

From: Jeff Thompson <jeff.thompson_at_e-markets.com>
Date: Thu, 11 Nov 1999 15:19:08 -0600
Message-ID: <382B32CC.356FFB90_at_e-markets.com>

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 Thu Nov 11 1999 - 22:19:08 CET