Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Friday May 14, 2004
01:27 PM

Did you know you could do this?

[ #18761 ]

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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I believe you must specify a size as a 3rd argument to bind_param_inout() unless things have changed since I last looked.
    • Yes; that's correct. Thanks for catching it! :)

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers