Did you know you could do this?

jdavidb on 2004-05-14T18:27:03

Problem: write a routine for Oracle using DBI that inserts a record into a table, using an Oracle sequence to generate the new ID for the record, and returns the record. The catch? Use only one SQL statement.

Solution:

sub new_record
{
  my($dbh, $field1, $field2) = @_;
  my $sql = <<"EOF";
  INSERT INTO mytable
  (id, field1, field2)
  VALUES
  (mysequence.NEXTVAL, ?, ?)
  RETURNING id
  INTO ?
EOF
  my $id;
  my $sth = $dbh->prepare($sql);
  $sth->bind_param(1, $field1);
  $sth->bind_param(2, $field2);
  $sth->bind_param_inout(3, \$id, 1024);
  $sth->execute;
  return $id;
}

The key is that RETURNING ... INTO clause, which is of course very Oracle specific. Many thanks to Steven Feuerstein and Bill Pribyl, authors of Oracle PL/SQL Programming (third edition), who taught me that this was possible, and for Tim Bunce, inventor of DBI and author of DBD::Oracle, for making it possible.

Updated 5/24/2004: fixed bug; thanks djberg

---

Minor error

djberg96 on 2004-05-14T18:55:32

I believe you must specify a size as a 3rd argument to bind_param_inout() unless things have changed since I last looked.

Re:Minor error

jdavidb on 2004-05-24T15:03:41

Yes; that's correct. Thanks for catching it! :)