Home » SQL & PL/SQL » SQL & PL/SQL » get ORA-04098 when inserting nextval into sequence nbr using trigger (oracle 9.2.0.5)
get ORA-04098 when inserting nextval into sequence nbr using trigger [message #356450] Thu, 30 October 2008 14:03 Go to next message
aft5425
Messages: 5
Registered: October 2008
Junior Member
Hi i created a table and a primary key for that table as follows:

create table PROD_TRNSLTN
(
PROD_PK VARCHAR2(16),
CNTRC_CD VARCHAR2(10),
PROD VARCHAR2(10),
PLN VARCHAR2(10)
);

alter table PROD_TRNSLTN
add constraint PROD_TRNSLN_PK primary key (PROD_PK);


i then created a sequence and trigger to automatically populate the primary key:

CREATE SEQUENCE PROD_PK_SEQ
MINVALUE 1
MAXVALUE 999999999
START WITH 1
INCREMENT BY 1
CACHE 20;


create trigger PROD_TRNSLTN_TRGR
before insert on PROD_TRNSLTN
for each row
begin
Select PROD_PK.nextval into :new.PROD_PK from dual;
end;

i get no syntax error, but when i use SQLLoader to insert rows into this table i get:

"ORA-04098: trigger 'IAVE.PROD_TRNSLTN_TRGR' is invalid and failed re-validation"

Why??
Re: get ORA-04098 when inserting nextval into sequence nbr using trigger [message #356453 is a reply to message #356450] Thu, 30 October 2008 14:39 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what you should do and how you should post:
SQL> create table PROD_TRNSLTN
  2  (
  3  PROD_PK VARCHAR2(16),
  4  CNTRC_CD VARCHAR2(10),
  5  PROD VARCHAR2(10),
  6  PLN VARCHAR2(10)
  7  );

Table created.

SQL> alter table PROD_TRNSLTN
  2  add constraint PROD_TRNSLN_PK primary key (PROD_PK);

Table altered.

SQL> CREATE SEQUENCE PROD_PK_SEQ
  2  MINVALUE 1
  3  MAXVALUE 999999999
  4  START WITH 1
  5  INCREMENT BY 1
  6  CACHE 20;

Sequence created.

SQL> create trigger PROD_TRNSLTN_TRGR
  2  before insert on PROD_TRNSLTN 
  3  for each row
  4  begin
  5  Select PROD_PK.nextval into :new.PROD_PK from dual;
  6  end; 
  7  /

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER PROD_TRNSLTN_TRGR:
LINE/COL
--------------------------------------------------------------
ERROR
--------------------------------------------------------------
2/1
PL/SQL: SQL Statement ignored
2/8
PL/SQL: ORA-02289: sequence does not exist

Then the error is obvious.

Regards
Michel
Previous Topic: Getting ORA-0060 Dead lock error
Next Topic: Partition Question
Goto Forum:
  


Current Time: Tue Dec 06 04:16:41 CST 2016

Total time taken to generate the page: 0.05840 seconds