Solved

Default nextval(sequence)

Posted on 2006-10-30
3
2,624 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Single ERP VS muttiple Application or Systems 6 61
performance tunning sql insert - challenging one 2 42
EXECUTE IMMEDIATE 5 51
Converting a row into a column 2 43
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now