Oh, wise and powerful Oz Monks!

I am new to DBI, and having some difficulty using an insert with the returning into clause. I am using Perl 5.6.0 and DBI 1.14 on Solaris 2.6 with Oracle 10g.

I am having difficulty understanding how to properly perform an INSERT with a RETURNING INTO clause. The SQL (full Perl code below) that is not working is: What I would like to accomplish is:

  INSERT INTO test
  VALUES ( ?,? )
  RETURNING my_id INTO ?
[download]

The table, test, was created with:

  CREATE TABLE "SPL"."TEST" (
    "MY_ID" NUMBER NOT NULL ENABLE, 
    "MY_NAME" VARCHAR2(50) NOT NULL ENABLE, 
    "MY_DESC" VARCHAR2(100), 
    CONSTRAINT "TEST_PK" PRIMARY KEY ("MY_ID")
  ) ;
[download]

The primary key, my_id, is populated with a sequence through the use of a trigger which fires before the insert.

The sequence is defined as:

  CREATE SEQUENCE  "SPL"."TEST_ID_SEQ"  
    MINVALUE 1 
    MAXVALUE 999999999999999999999999999 
    INCREMENT BY 1 
    START WITH 1020 
    CACHE 20 
    NOORDER  
    NOCYCLE ;
[download]

and the trigger is defined as:

  CREATE OR REPLACE TRIGGER "SPL"."BFR_INS_TEST_TRG" 
    before insert on TEST
    for each row
  
  declare
    cursor test_id_cur is
      select test_id_seq.nextval
        from dual;
  begin
    if :new.my_id is null then
      open test_id_cur;
      fetch test_id_cur into :new.my_id;
      close test_id_cur;
    end if;
  end BFR_INS_TEST_TRG;

  /
  ALTER TRIGGER "SPL"."BFR_INS_TEST_TRG" ENABLE;
[download]

The program:

#!/usr/bin/perl

use strict;
use warnings;

use DBI qw(:sql_types);

print $DBI::VERSION, "\n";
exit;

my $dbh = DBI->connect('dbi:Oracle:db','user','pass',
    { AutoCommit => 0, RaiseError => 0, PrintError => 0 }
) or die "Unable to connect!: $!\n";

my $sql = qq {
    INSERT INTO peck_test
    VALUES ( ?,? )
    RETURNING my_id INTO ?
};
my $sth = $dbh->prepare($sql);

my $id;
my $name = 'Bob';
my $desc = 'Just another guy';

$sth->bind_param      (1, $name, SQL_VARCHAR);
$sth->bind_param      (2, $desc, SQL_VARCHAR);
$sth->bind_param_inout(3, \$id,  SQL_NUMERIC);

$sth->execute();

print "Inserted record: $id\n" if $id;

$sth->finish();
$dbh->disconnect();
[download]

This results in no record being inserted, and likewise no value returned.

This is a much simpler version of what I am actually trying to do, but the method is the same. I have also tried bind_col and bind_columns.

As always, any corrections, suggestions, etc., are greatly appreciated.

Thanks, Akoya