Home » SQL & PL/SQL » SQL & PL/SQL » Problem running trigger with sequence
Problem running trigger with sequence [message #6623] Tue, 29 April 2003 03:49 Go to next message
Dawn
Messages: 10
Registered: April 2001
Junior Member
Hi all,

I tried to create a sequence in sqlplus using:

CREATE SEQUENCE AUTONUMSEQ START WITH 1 INCREMENT BY 1

Then I created a trigger as follows:

CREATE TRIGGER CARETRIGGER before insert on TableA
for each row
begin
select AUTONUMSEQ.nextval into :new.id from dual;
end;
/

When i tried to insert a record into TableA using jsp, I got the following error:

java.sql.SQLException: ORA-04016: sequence AUTONUMSEQ no longer exists ORA-06512: at "VIGNETTE.CARETRIGGER", line 2 ORA-04088: error during execution of trigger 'VIGNETTE.CARETRIGGER'

But when i did a select on user_sequence table it shows the sequence - AUTONUMSEQ exists

Is the sequence name case sentitive??

I'm stuck here, could anybody help??
Re: Problem running trigger with sequence [message #6624 is a reply to message #6623] Tue, 29 April 2003 04:30 Go to previous messageGo to next message
Balaji
Messages: 102
Registered: October 2000
Senior Member
check whether u are using the same user account for creation of sequence and execution of trigger.
also try the below query and make sure that sequence exists in the schema

select autonumseq.nextval from dual;

revert for further clarifications.
Re: Problem running trigger with sequence [message #6625 is a reply to message #6624] Tue, 29 April 2003 04:57 Go to previous messageGo to next message
Dawn
Messages: 10
Registered: April 2001
Junior Member
i tried to run the following command in sqlplus:

select autonumseq.nextval from dual;

i'm able to get the result 1, when i run again, it increment to 2. but the strange thing is when i tried to execute the same sql command using my jsp, it says:

java.sql.SQLException: ORA-04016: sequence AUTONUMSEQ no longer exists

i'm sure that i use the same user account 'vignette' to create both the sequence and trigger.
Re: Problem running trigger with sequence [message #6628 is a reply to message #6623] Tue, 29 April 2003 11:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Dawn, I don't have an answer for you but I can confirm that your sequence and trigger are created correctly. The error message description indicates that the sequence is somehow getting dropped, but you said that it still shows in user_sequences.

You incremented the sequence from a SQL*Plus prompt - does the insert statement also work from the prompt?
Re: Problem running trigger with sequence [message #6635 is a reply to message #6628] Wed, 30 April 2003 00:43 Go to previous messageGo to next message
Dawn
Messages: 10
Registered: April 2001
Junior Member
Hi Todd,

It seems wierd to me too. But the trigger actually works when i tried to insert from the sqlplus prompt. It just won't work on my JSP web application even though i use the same SQL. I'm so lost!
Re: Problem running trigger with sequence [message #6642 is a reply to message #6635] Wed, 30 April 2003 04:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The full text of the error message is:

"ORA-04016 sequence string no longer exists

Cause: Sequence was dropped while processing its next value.

Action: Re-create the sequence."

Since everything works without the java, the problem must be somewhere in the java. Perhaps you could post your java code, so we could test and debug it. You might try recreating the sequence once as suggested above, but if the problem recurs then you need to find a better way. Java has some weird quirks to it. For example, on some Oracle versions, if you are using the syntax "create or replace and compile java source named" you have to run it twice.
Re: Problem running trigger with sequence [message #216764 is a reply to message #6642] Tue, 30 January 2007 05:22 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
I am sorry to dig up such an old thread but I am experiencing this problem at the moment.

The story goes like this:
1. I have a big sql script (10000+ lines) for creating Users(schemas) and tables
2. I run this script in SQL*Plus command prompt (ie: NOT SQL Worksheet)
3. After the script finished I checked the spool file and found the following error:
old   1: INSERT INTO &HKDB_Schema_Name..HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
new   1: INSERT INTO PL1DB.HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
INSERT INTO PL1DB.HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
ERROR at line 1:
ORA-04016: sequence HKCOLUMNTYPE_ID_SEQ no longer exists 
ORA-06512: at "PL1DB.HKCOLUMNTYPE_ID_TRIG", line 2 
ORA-04088: error during execution of trigger 'PL1DB.HKCOLUMNTYPE_ID_TRIG'

However, just a few lines above I have:
old   1: CREATE SEQUENCE &HKDB_Schema_Name.."HKCOLUMNTYPE_ID_SEQ" INCREMENT BY 1 START
new   1: CREATE SEQUENCE PL1DB."HKCOLUMNTYPE_ID_SEQ" INCREMENT BY 1 START

Sequence created.

Commit complete.


I have tried manually run the above command, dropping the sequence then recreated it, commited the changes to the database and ran the queries and I still have the same problem.

I also checked that the sequence is listed in the list or sequence in the Enterprise Management Console, but when I use the test query given by Balaji I got the same error message.

I am using Oracle 9i R2 Production (9.2.0.1.0).

Thank you very much.
Re: Problem running trigger with sequence [message #216912 is a reply to message #216764] Tue, 30 January 2007 19:33 Go to previous message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Bump!!!

Hope to attract some more attentions.

Thanks guys and gals!!

I locked this one as you started a new topic, which was probably better than continuing in the very old thread.

[Updated on: Wed, 31 January 2007 14:30] by Moderator

Report message to a moderator

Previous Topic: Creating Oracle Directories
Next Topic: Stored procedure help
Goto Forum:
  


Current Time: Fri Dec 09 09:27:45 CST 2016

Total time taken to generate the page: 0.06580 seconds