Re: automatic PK generation?

From: Holger Heidenbluth <heidenbluth_at_nis-rheinsberg.de>
Date: Thu, 12 Apr 2001 09:17:55 +0200
Message-ID: <3ad556f7$0$20636$4dbef881_at_businessnews.de.uu.net>

The following example allows you both
- Select sequence value from the client application and

   pass the ID value together with the INSERT statement    to the server.
- Omit the ID and let the server select a new value from   the sequence.

Holger.

CREATE OR REPLACE TRIGGER MY_TABLE_BIR
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
BEGIN
  SET_ID_FROM_SEQ( :new.ID, 'MY_TABLE'); END;
/

CREATE OR REPLACE
procedure set_id_from_seq( AId in out number, ASequence in varchar2) is
  seq_not_in_use exception;
  pragma exception_init( seq_not_in_use, -8002);   cv number;
  cur integer;
  ret integer;
begin
  if ASequence is not null then
    cur := dbms_sql.open_cursor;
    if AId is null then
      dbms_sql.parse( cur, 'select ' || ASequence || '.nextval from dual', 2);

      dbms_sql.define_column( cur, 1, AId);
      ret := dbms_sql.execute_and_fetch( cur, true);
      dbms_sql.column_value( cur, 1, AId);
    else
      dbms_sql.parse( cur, 'select '|| ASequence || '.currval from dual',
2);
      dbms_sql.define_column(cur , 1, cv);
      ret := dbms_sql.execute_and_fetch( cur, true);
      dbms_sql.column_value( cur, 1, AId);
      if AId > cv then
        raise_application_error(-20502, 'ID value exceeds CURRVAL of
sequence  ' || ASequence);
      end if;

    end if;
    dbms_sql.close_cursor(cur);
  end if;
exception
  when seq_not_in_use then
    if dbms_sql.is_open(cur) then
      dbms_sql.close_cursor(cur);
    end if;
    raise_application_error(-20502, 'Please use the sequence ' || ASequence || ' to insert the ID.');
  when others then
    if dbms_sql.is_open(cur) then
      dbms_sql.close_cursor(cur);
    end if;
      raise;
end set_id_from_seq;
/

"Gabriel Belingueres" <belingueres_at_lycos.com> schrieb in im Newsbeitrag: PKLKFFHNJDHCLAAA_at_mailcity.com...
> Hi
>
> I need that Oracle 8i automatically generates primary keys for a
particular table. In others databases that data type is known as SERIAL or something, but I can't find how Oracle provides that functionality.
>
> Which is the Oracle 8i datatype or keyword to do that?
>
> Regards,
> Gabriel
>
>
>
> Get 250 color business cards for FREE! at Lycos Mail
> http://mail.lycos.com/freemail/vistaprint_index.html
>
> --
> Posted from fes.whowhere.com [209.185.123.154]
> via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Thu Apr 12 2001 - 09:17:55 CEST