Solved

Default nextval(sequence)

Posted on 2006-10-30
3
2,635 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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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
oracle DR - data guard failover. 18 72
setting local variables in a cursor block 3 41
Password_rules_securitty.. 12 46
Oracle Date 6 37
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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