Re: Getting primary key right after insertion into the record

What he means is if you use PL/SQL, you can get the primary key right on the insert statement using the retuning clause like this (untested):

declare theempid number;
begin
 insert into emp (lastname) values ('Huang') returning empid into theempid;  dbms_output.put_line(theempid);
end;
/

I don't think this works in any language other than PL/SQL. The problem is that you can't pass that returned value to your Java or whatever program.

Normally you should not do select seq.nextval before you insert. Otherwise when you insert, it's going to take the next value of the sequence (unless your trigger has "if :new.empid is null" and you specify seq.currval in your insert). Besides, select seq.nextval into only works in PL/SQL anyway. If you don't use "into a_variable" and capture the query output in your Java environment, then you still have to do another executeUpdate anyway, equivalent to simply insert and select from that table to see what the sequence current value is.

Yong Huang
yhuang_at_indigopool.com

Darwin Ling <dling_at_one-umbrella.com> wrote in message news:8ptm4f$jo0$1_at_news.jump.net...
> I am using Oracle 8i.
>
> I don't quite understand what you mean by the returning clause. I just
> called statement.executeUpdate("insert....");
>
> And the returning type of this executeUpdate is the number of rows
 affected.
>
> Please clarify. Thanks
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:969035331.1629.0.pluto.d4ee154e_at_news.demon.nl...
> > There's no need to do it that way.
> > In Oracle 8.0 and before you can simply select <sequencename>.nextval
 into
> > <anyvariable>
> > before you do the insert
> > In Oracle 8i and beyond you can use the returning clause of the insert
> > command.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > "Jump.Net" <darwin_ling_at_hotmail.com> wrote in message
> > news:8ptego$92m$1_at_news.jump.net...
> > > How do I obtain the primary key ID right after a record is inserted in
> > > Oracle?
> > >
> > > Such primary key is generated by trigger/sequence. And I am doing it
 within
> > > a Java JDBC environment
> > >
> > > And this has to work in a multithreaded environment.
> > >
> > > I thought about using
> > >
> > > select max(id) from table
> > >
> > > right after the insert.
> > >
> > > Would this be the best way?
> > >
> > >
> >
> >
>
>
Received on Fri Sep 15 2000 - 14:26:20 CDT