Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Clarification of 'Sequences'

Re: Clarification of 'Sequences'

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: 1998/09/30
Message-ID: <36134bc4.5157155@dcsun4.us.oracle.com>#1/1

On Wed, 30 Sep 1998 10:08:18 -0400, "steve hendrikse" <shend_at_sar-net.com> wrote:

>Good Day,
>
>I have reasonable experience with both PostgreSQL and Micrsoft SQLServer.
>WIth both I can create a column in a table that automatically generates a
>unique count value. That is:
>
>in MSSQL, i do:
> create tabel test1(
> id int identity(1,1) not null,
> value varchar(80)
> );
>
>in PostgreSQL, it is slightly more complex, but similar to:
>
> create sequence seq_id start 1 increment 1;
> create table test2(
> id int4 default nextval('seq_id') not null,
> value varchar(80)
> );
>
>now, in Oracle 8.0.4 (on Solaris), i believe that i must do something
>similar to the PostgreSQL case. I start with:
>
> create sequence seq_id start with 1 increment by 1;
>
>and then (?????)
>
> create table
>
> id int default seq_id.nextval not null,
> value varchar(80)
> );
>
>but this gets me an error saying that i cannot include a column name
>(seq_id) in the table definition.
>
>now, i conclude that if both MSSQL and PostgreSQL can do this (as shown
>above) there has to be a 'graceful' way to get Oracle to do it also.
>

Yup, there is.

You need to create a trigger on the table before insert.

eg.

SQL> create sequence seq_id start with 1 increment by 1;

Sequence created.

SQL> create table T(
  2 id number,
  3 value varchar2(80)
  4 );

Table created.

SQL> create or replace trigger bi_t
  2 before insert on T
  3 for each row
  4 begin
  5 select seq_id.nextval

  6      into :new.id
  7      from dual;

  8 end;
  9 /

Trigger created.

SQL> insert into t ( value ) values ( 'Test' );

1 row created.

SQL> insert into t ( value ) values ( 'Test Again' );

1 row created.

SQL> col value for a20
SQL> select * from T;

        ID VALUE

---------- --------------------
         1 Test
         2 Test Again



Hope this helps.

chris.

>please help!!!
>
>thanks in advance,
>steve
>
>
>
Received on Wed Sep 30 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US