How do I use a sequence (nextval) as a default value in a create table statement ORA

[Michel Cadot]

You can't do that. You have to use a before trigger for each row
something like:
v734> create table test (test1 number not null);
Table created.
v734> create or replace trigger test_bi before insert on test
2 for each row
3 declare
4 foo number;
5 begin
6 if :new.test1 is null then
7 select sq_test.nextval into foo from dual;
8 :new.test1 := foo;
9 end if;
10 end;
11 /
Trigger created.
v734> create sequence sq_test;
Sequence created.
v734> insert into test values (10);
1 row created.
v734> insert into test values(null);
1 row created.
v734> select * from test;

TEST1
----------
10
1

2 rows selected.

--
Have a nice day
Michel

Stephen Hurrell <hurr...@hotmail.com> a écrit dans le message : 38EC97AE...@hotmail.com...
> Hello.
>
> I am getting an "ORA-00984: column not allowed here" when I attempt to
> use a sequence (SQ_TEST) in a create table statement as a default value.
> Sequence is defined and works. Oracle 8.0.5 on Linux 2.2.13.
>
> CREATE TABLE TEST (
> TEST1 NUMBER (14) DEFAULT SQ_TEST.NEXTVAL NOT NULL ) ;
>
> This is also wrong
>
> CREATE TABLE TEST (
> TEST1 NUMBER (15) DEFAULT select sq_cpic.nextval from dual NOT NULL
> ) ;
>
>
> --
> STeve
>
>

[Vitalij Serdakovskij]

You need to create a before insert trigger.

--
Good luck!
Wit, Oracle DBA

"Stephen Hurrell" <hurr...@hotmail.com> сообщил/сообщила в новостях следующее: news:38EC97AE...@hotmail.com...

[Xitix]

From Orcale docs

The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified

 

You can however accomplish what you need by using a trigger to assign the sequence to the field as rows are added.

 

 

Barry

Stephen Hurrell <hurr...@hotmail.com> wrote in message news:38EC97AE...@hotmail.com...