Re: how to define a pseudo auto value column in oracle?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 8 Feb 2005 08:05:09 -0800
Message-ID: <1107878709.087390.253490@l41g2000cwc.googlegroups.com>

Robert Wehofer wrote:
> Hello there!
>
> One of the most hated features in Oracle are the intricated use of
sequences
> to create valid IDs for a prim key column. In Access you've got the
data
> type Autovalue, which is solid and simple. In Oracle you need to know
the
> name of the sequence and have to call nextval, if you want to insert
a
> dataset. Is there a way to automate the setting of the ID using
sequences?
> One way is to use insert triggers, but is there a possibility to
define a
> sequence as default value for a prim key value? If yes, how would be
the
> syntax of the 'create table' statement? And is ADO able to return the
data
> type Autovalue, if a column in Oracle has got a sequence as default
value?
>
> Regards,
> Robert

Can you store few million rows in a table in Access and have couple of thousand users query it 24/7? You guessed it right!! There is no comparison between a grape and a water mellon so there is no need for any negative comments.

Now to your question,
If you want to auto assign a sequence value then you can either create a before insert trigger or use procedure to insert values into a table. Here is a simple trigger example... Note the insert statements... there is no mention of ID or sequence,

SQL> create table foo (id number, my_value varchar2(10));

Table created

SQL> alter table foo add constraint foo_pk primary key (id);

Table altered

SQL> create sequence foo_seq minvalue 1 maxvalue 100000000 start with 1 increment by 1;

Sequence created

SQL> create or replace trigger foo_trg
  2 before insert on foo
  3 referencing old as old new as new
  4 for each row
  5 declare
  6 id_ number;
  7 begin

  8     select foo_seq.nextval into id_ from dual;
  9     :new.id := id_;

 10 end;
 11 /

Trigger created

SQL> select * from foo;

        ID MY_VALUE
---------- ----------

SQL> insert into foo (my_value) values ('A');

1 row inserted

SQL> insert into foo (my_value) values ('B');

1 row inserted

SQL> insert into foo (my_value) values ('C');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from foo;

        ID MY_VALUE
---------- ----------

         1 A
         2 B
         3 C

Regards
/Rauf Received on Tue Feb 08 2005 - 10:05:09 CST