If you have Oracle 8 (I missed the start of this thread, so I don't know
what you're using, sorry), you can use a RETURNING clause with an UPDATE.
Something like this (untested):
> #!/usr/local/bin/perl
> use strict;
>
> my $sql = q{ INSERT INTO REPORT
VALUES ( REPORT_NUM_SEQ.NEXTVAL,
:report,
to_date( :date_reported, 'DD-MON-YYYY
HH24:MI'))
RETURNING report_num_column
INTO :report_num };
my $sth = $dbh->prepare($sql);
$sth->bind_param(':report', $report);
$sth->bind_param(':date_reported',$date_reported);
$sth->bind_param_inout(':report_num',\$rep_no, 40);
$sth->execute();
etc....
Same solution as Michael's, just a bit less code ;-]
The version of DBI or DBD::Oracle that you're using should not affect this,
AFAIK, but I'm pretty sure that Oracle didn't add the RETURNING clause to
INSERT and UPDATE until one of the Oracle 8 variants.
Simon
> ----------
> From: Michael A Chase
> Sent: Wednesday, February 13, 2002 8:02 AM
> To: [EMAIL PROTECTED]; Brett W. McCoy
> Cc: DBI Users
> Subject: Re: Returned value from insert
>
> 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')).
>
>
>
>
>
***********************************************************************
This e-mail and its attachments are intended for the above named
recipient(s) only and are confidential and may be privileged.
If they have come to you in error you must take no action based
on them, nor must you copy or disclose them or any part of
their contents to any person or organisation; please notify the
sender immediately and delete this e-mail and its attachments from
your computer system.
Please note that Internet communications are not necessarily secure
and may be changed, intercepted or corrupted. We advise that
you understand and observe this lack of security when e-mailing us
and we will not accept any liability for any such changes,
interceptions or corruptions.
Although we have taken steps to ensure that this e-mail and its
attachments are free from any virus, we advise that in keeping
with good computing practice the recipient should ensure they
are actually virus free.
Copyright in this e-mail and attachments created by us belongs
to Littlewoods.
Littlewoods takes steps to prohibit the transmission of offensive,
obscene or discriminatory material. If this message contains
inappropriate material please forward the e-mail intact to
[EMAIL PROTECTED] and it will be investigated.
Statements and opinions contained in this e-mail may not
necessarily represent those of Littlewoods.
Please note that e-mail communication may be monitored.
Registered office:
Littlewoods Retail Limited,
Sir John Moores Building,
100 Old Hall Street,
Liverpool,
L70 1AB
Registered no: 421258
http://www.littlewoods.com
***********************************************************************
