Re: Getting primary key right after insertion into the record

Just use the returning clause for the insert statement

eg
Create or Replace Function InsertInfo(MyRecord MyTable%RowType) Return Number
is
Result :Integer;
begin

    insert into mytable
    (line_id
    ,description0
    values
    (line_id_s.nextval
    ,myrecord.description)
    Returning line_id into Result;

    Return Result;

    Exception
    When others then
    Return null;
 end;
end;

If using Java return line_id into a bind variable (:result) .

"spencer" <spencerp_at_swbell.net> wrote in message news:T8Bw5.166$mC1.104223_at_nnrp2.sbc.net...
> 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 - 15:33:14 CDT