Solved

Default nextval(sequence)

Posted on 2006-10-30
3
2,628 Views
Last Modified: 2008-02-01
Hi I am want to alter primary key column to Default nextval(sequence)
I am getting fallowing error

ALTER TABLE emp
MODIFY(ID  DEFAULT nextval(SEQ_EMPNO))
ORA-00984: column not allowed here

Can you please tell me how to alter table with default value with squence

Thanks
0
Comment
Question by:basirana
3 Comments
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 17834281
Hi,
   This is not possible with default clause.
You will need to create a before insert trigger to accomplish this.
HTH
Regards,
Badri.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 17834305
create trigger emp_ins
on emp
before insert
for each row
begin
  select seq_empno.nextval
    into :new.id
    from dual;
end;
/
0
 
LVL 12

Expert Comment

by:jwahl
ID: 17834327
CREATE OR REPLACE TRIGGER EMP_IBR
 BEFORE INSERT
 ON EMP
 FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
       SELECT seq_empno.nextval
       INTO   :NEW.id
       FROM   dual;
    END IF;
END;
/
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 58
use lov values 2 61
Oracle - Query link database loop 8 40
Oracle Nested table uses ? 2 31
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question