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

In reply to INSERT with RETURNING INTO clause by Akoya

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.