?
Solved

Default nextval(sequence)

Posted on 2006-10-30
3
Medium Priority
?
2,643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

Accepted Solution

by:
johnsone earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

770 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