Re: Obtaining Rowid after insert into ... values ...
Date: 1996/11/07
Message-ID: <3282062d.4934034_at_dcsun4>#1/1
On Fri, 08 Nov 1996 00:55:41 +1000, Neville Grills <nevilleg_at_deakin.edu.au> wrote:
>Is there a way of getting the rowid of the row inserted into
>a non-keyed table immediately after an insert into ... values (...)
>in PL/SQL or SQL*Plus. Forms must be able to do it as it can update the
>inserted record, and it appears to be available to the OCI via the Comms
>area, but I can't find any documentation on finding it in Oracle
>manuals.
>
>Help would be appreciated.
>
>Neville ( replies by e-mail would be appreciated :)
the safest way to do this would be via a trigger and package variable. You can get it from oci but it's pretty hairy. The general code would look like:
create or replace package last
as
rid rowid;
end;
/
create or replace trigger xxx_aifer
after insert
on xxx
for each row
begin
last.rid := :new.rowid;
end;
/
That way, you would have the rowid of the last inserted row available in the pl/sql variable. In sql*plus you could:
SQL> insert into xxx values ( ..... );
SQL> exec dbms_output.put_line( last.rid );
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Nov 07 1996 - 00:00:00 CET