Re: Record Numbers in Oracle Tables

From: Mark Malakanov <markmal_at_sprint.ca>
Date: Sun, 10 Oct 1999 19:17:52 -0300
Message-ID: <Zi9M3.3099$j35.98717@newscontent-01.sprint.ca>

What do you mean on "record id" ?

You can use trigger to return info you interested.

drop table T;
drop sequence SQ_T_ID;

create table T (id number, name varchar2(30)); create sequence SQ_T_ID;

create or replace trigger TTb before insert or update on T for each row begin
--fill ID from sequence
select SQ_T_ID.nextval into :new.id from dual; end;
/

create or replace trigger TTa after insert or update on T for each row begin
dbms_application_info.set_client_info(   'lastrowid='|| :new.rowid
  ||' last ID='|| :new.id );
end;
/

--Emulation of application work

declare CI varchar2(255);
begin
insert into T (name) values ('Ian Steward');

sys.dbms_application_info.read_client_info(CI);
sys.dbms_output.put_line('client info: '||CI);
--....

commit;
end;
/

If you already use Application_info for other needs, you can use dbms_pipe package, or your own package to transfer values from trigger to application. In case of own package values will visible only for your session. In case of Application_info or dbms_pipe values will visible for any session who will read from this (After being set, the client info field can be queried from v$session.). Messages will desappear from pipe after being read.

Mark Malakanov
OraDBA
Sapience, Toronto

Ian Steward <isteward_at_mediaone.net> wrote in message news:IVUL3.9985$Ud2.334442_at_typhoon1.rdc-detw.rr.com...
> Is there a way to return the record id after an INSERT or UPDATE ? I am
> looking for a similar capability to the IDENTITY function in SQL Server.
>
> - Ian Steward
>
>
Received on Sun Oct 10 1999 - 17:17:52 CDT