The SELECT is standard Oracle SQL, it will work on any version of
DBD::Oracle.
You could also prepare and execute an anonymous block that SELECTs
seq_name.nextval into a PL/SQL variable or bind variable which is uses in an
INSERT. That leaves you with just one round-trip to the database.
# Untested, mostly from memory.
# See DBD-Oracle-1.12/Oracle.ex/ for examples (also in DBD-Oracle-1.06).
$dbh -> {RaiseError} = 1;
$sth = $dbh -> prepare( <<END_BLOCK );
BEGIN
SELECT seq_name.nextval INTO :seq_var FROM DUAL;
INSERT INTO table ( k, v ) VALUES ( :seq_var, :v_var );
END;
END_BLOCK
$sth -> bind_param_inout( ":seq_var", \$nSeq, 40 );
# Sometime later, maybe in a loop
$sth -> bind_param( ":v_var", $sV );
$sth -> execute;
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Stacy Mader" <[EMAIL PROTECTED]>
To: "Brett W. McCoy" <[EMAIL PROTECTED]>
Cc: "DBI Users" <[EMAIL PROTECTED]>
Sent: Tuesday, February 12, 2002 22:39
Subject: Re: Returned value from insert
> Brett,
>
> Your right. From the DBD::Oracle (v1.12) doco -
>
> INSERT INTO table (k, v) VALUES (seq_name.nextval, ?)
>
> To get the value just inserted you can use
>
> SELECT seq_name.currval FROM DUAL
>
> This was from the DBD::Oracle v1.12 doco. I
> currently have v1.06!
>
>
> "Brett W. McCoy" wrote:
> >
> > On Wed, 13 Feb 2002, Stacy Mader wrote:
> >
> > > #!/usr/local/bin/perl
> > >
> > > use strict;
> > >
> > > my $sql = q{
> > > INSERT INTO REPORT
> > > VALUES (REPORT_NUM_SEQ.NEXTVAL,?,to_date(?,'DD-MON-YYYY HH24:MI')
> > > ),
> > > undef, $report, $date_reported
> > > };
> > >
> > > Where the report number is incremented via the
> > > REPORT_NUM_SEQ.NEXTVAL statement (REPORT_NUM_SEQ
> > > is an Oracle sequence).
> >
> > Can't you do a query and retrieve REPORT_NUM_SEQ.CURRVAL (something like
> > SELECT REPORT_NUM_SEQ.CURRVAL) after you do the insert? That should be
> > the value that was just inserted from the sequence (going by my rusty
> > Oracle knowledge -- I remember that it was very similar to PostgreSQL's
> > sequences, where you would do currval('report_num_seq')).
