selecting max(id) from the table is _not_ the best way to get the id
value of the last inserted row. there is some degree of probability
that the query will return the correct value, but it's not guaranteed.
The following technique will work In Oracle 8.0.6, and should work
with 8.1.x as well.
Create either a PL/SQL stored function (or a procedure with an OUT
argument), to perform the insert. Something like this
CREATE FUNCTION my_foo
(as_val2 IN VARCHAR2
,as_val3 IN VARCHAR2
,as_commit IN VARCHAR2 DEFAULT 'Y'
)RETURN VARCHAR2
IS
ln_id NUMBER;
BEGIN
SELECT myseq.NEXTVAL INTO ln_id;
INSERT INTO mytable
(mykey1, mycol2, mycol3)
VALUES
(ln_id, as_val1, as_val2);
IF as_commit = 'Y' THEN
COMMIT;
END IF;
RETURN TO_CHAR(ln_id);
END;
/
then from your java code, use a JDBC "callable statement" to execute
the stored procedure. the string containing the statement text would look
something like this:
" begin ? := foo( ? , ? ) ; end ; "
bind the return value and the arguments to string variables, and then
execute the statement.
HTH
"Yong Huang" <yhuang_at_indigopool.com> wrote in message news:8ptt9s$jqn$1_at_news.sinet.slb.com...
> 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 - 21:45:51 CDT
