Skip to Main Content
  • Questions

  • sequence TEST_SEQ.CURRVAL is not yet defined in this session\n (8002) (SQLExecDirectW)')

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 04, 2008 - 11:57 am UTC

Last updated: September 15, 2009 - 10:09 am UTC

Version: 8.2

Viewed 10K+ times! This question is

You Asked

Hi,

I've used the following statements to create a table with an index which is automatically incremented when a record is added.

create table teststations (
teststation_index NUMBER PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL
);

create sequence test_seq
start with 1
increment by 1
nomaxvalue;

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON teststations
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_seq.nextval INTO :NEW.teststation_index FROM dual;
END;

This works as expected and I have records with unique index values. But the statement

select test_seq.currval from dual;

causes the error message:

ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

According to the snippets I've found on the internet, this statement should give me the latest index value.

Thanks for any help,

Martin.

and Tom said...

(I hate triggers, especially triggers like this, useless triggers that "save you work" - bah, they save you nothing, they make things take longer to process, are not convenient, make things more confusing (proof: you are confused here already and you are just in development, wait'll maintenance when someone has to maintain this "automagic side effect code")



you have not said what the programming environment looks like here but I'll guess.

You are using a connection pool

You grab a connection - do the insert.
You grab another connection - do the select.

the two sessions are NOT related to each other at all.

So, do this in ONE CONNECTION POOL GRAB.

Better yet, use the insert returning to do it all at once:


ops$tkyte%ORA9IR2> create sequence s;

Sequence created.

ops$tkyte%ORA9IR2> create table t ( x int primary key, y int );

Table created.

ops$tkyte%ORA9IR2> create or replace trigger t before insert on t for each row
  2  begin
  3          select s.nextval into :new.x from dual;
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> variable x number
ops$tkyte%ORA9IR2> insert into t (y) values ( 42 ) returning x into :x;

1 row created.

ops$tkyte%ORA9IR2> print x

         X
----------
         1


or better, better yet - lose the trigger (and gain performance, readability, understandability, maintainability) and just


ops$tkyte%ORA9IR2> drop trigger t;

Trigger dropped.

ops$tkyte%ORA9IR2> insert into t (x,y) values ( s.nextval, 42 ) returning x into :x;

1 row created.

ops$tkyte%ORA9IR2> print x

         X
----------
         2

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

comments on Triggers

reader, June 04, 2008 - 1:03 pm UTC

Dear Tom,

good day to you as always, I was going through the comments you have given I have never used triggers in any of my projects till date but just wanted to have an understanding from you, are triggers still not good to be used even in 10g or 11g for that matter.


Thanks in advance for you time and efforts.

Regards,
your fan.
Tom Kyte
June 04, 2008 - 1:11 pm UTC

The title of the last column I submitted for Oracle Magazine (undergoing editing now) is:

The Trouble with Triggers

Those of you who follow the asktom.oracle.com website know that I have an aversion to triggers. Once upon a time, a long time ago, I thought triggers were the coolest thing ever and I used (and abused) them heavily. Now, I will go very far out of my way in order to avoid a trigger - whenever possible.

Version & Client

Yogesh Purabiya, June 05, 2008 - 7:30 am UTC

I tried your example and got the following error

ORA-00439: feature not enabled: RETURNING clause from this client type

It was SQL*Plus and Oracle 8.1.7.

Is (Was ?!) RETURNING supported on oracle 8.1.7 ?
Tom Kyte
June 05, 2008 - 10:17 am UTC

I don't even bother trying things on 817 anymore. 9iR2 is as far back as I go...

it is a sqlplus'ism, sqlplus didn't recognize the returning. but we can do it still by wrapping in a plsql block in plus:

  1  begin insert into t (x,y) values ( s.nextval, 42 ) returning x
  2  into :x;
  3* end;
ops$tkyte@ORA817DEV> /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> print x

         X
----------
         1

ay to avoid the nextval call in a new session and get the currval

Vinu, September 14, 2009 - 2:16 am UTC

Is there a way to avoid the nextval call in a new session and get the currval for the sequence ? We are using 2 sequences to form unique string ids like
'A000001','A000002',....'A999999','B000001'... etc where for generating the alphabet I have a seqeunce as below.

create sequence syn_id_char_seq
start with 1
maxvalue 26
cycle
cache 25
increment by 1;

To get character I am using below SQL.

select chr(syn_id_char_seq.nextval + 64) from dual

For generating the 6 digit number I have the below sequence.

create sequence syn_id_seq
start with 1
maxvalue 999999
cycle
cache 1000
increment by 1;

I will move 'A' to 'B' (and so on till 'Z') only when syn_id_seq sequence goes back to 1. Meaning

id = syn_id_seq.nextval;
if id = 1 then
charValue = chr(syn_id_char_seq.nextval + 64);
end if

The issue is that, we are having this piece code deployed in a application server and we use connection pools. So for each connection in the connection pool, it is not possible to make a nextval call. Is there a way to get the currval without making nextval call in a session ? Is it a good idea to get the last_number from user_sequence table (I am using cache 25 while creating the sequence)

We are using Oracle 11.1.0.6

-Vinu


Tom Kyte
September 14, 2009 - 1:47 pm UTC

none of your code will work.

nextval might NEVER EVER return 1 to anyone. Sequences are not gap free.

and there is no reason why that syn_id_char_seq could not, would not apparently jump many values.

All it takes is ONE SESSION saying "rollback" and your entire scheme is destroyed.


ops$tkyte%ORA11GR1> create sequence s;

Sequence created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select chr( ascii('A')+trunc(s.nextval/1000000) ), s.nextval,
  2         chr( ascii('A')+trunc(s.nextval/1000000) ) || to_char(mod(s.nextval,1000000),'fm000000')
  3    from (select level l from dual connect by level <= 4 );

C    NEXTVAL CHR(ASCI
- ---------- --------
A          1 A000001
A          2 A000002
A          3 A000003
A          4 A000004

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> alter sequence s increment by 999993;

Sequence altered.

ops$tkyte%ORA11GR1> select s.nextval from dual;

   NEXTVAL
----------
    999997

ops$tkyte%ORA11GR1> alter sequence s increment by 1;

Sequence altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select chr( ascii('A')+trunc(s.nextval/1000000) ), s.nextval,
  2         chr( ascii('A')+trunc(s.nextval/1000000) ) || to_char(mod(s.nextval,1000000),'fm000000')
  3    from (select level l from dual connect by level <= 4 );

C    NEXTVAL CHR(ASCI
- ---------- --------
A     999998 A999998
A     999999 A999999
B    1000000 B000000
B    1000001 B000001

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> alter sequence s increment by 999996;

Sequence altered.

ops$tkyte%ORA11GR1> select s.nextval from dual;

   NEXTVAL
----------
   1999997

ops$tkyte%ORA11GR1> alter sequence s increment by 1;

Sequence altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select chr( ascii('A')+trunc(s.nextval/1000000) ), s.nextval,
  2         chr( ascii('A')+trunc(s.nextval/1000000) ) || to_char(mod(s.nextval,1000000),'fm000000')
  3    from (select level l from dual connect by level <= 4 );

C    NEXTVAL CHR(ASCI
- ---------- --------
B    1999998 B999998
B    1999999 B999999
C    2000000 C000000
C    2000001 C000001


will work without "magic", you need a max to ensure you don't go past "Z", but other than that - don't try magic when math will work.



math *is* magic

Sokrates, September 14, 2009 - 3:32 pm UTC

the greatest piece of magic !

Be careful if on RAC

Pasko, September 15, 2009 - 8:25 am UTC 

Hi Vinu,

Apart from what Tom said, be very careful with Sequences with CACHE if you are on RAC.

For example, i created this Sequence on my RAC 2 node DB and selected nextval from each instance.

create sequence syn_id_seq
start with 1
maxvalue 999999
cycle
cache 1000
increment by 1;


Instance 1.

SQL> select syn_id_seq.nextval from dual;

   NEXTVAL
----------
         1


Instance 2:

SQL> select syn_id_seq.nextval from dual;

   NEXTVAL
----------
      1001

Tom Kyte
September 15, 2009 - 10:09 am UTC

and since sequences are not gap free, and do not imply any sort of ordering (sequence = 1 does not mean the data it is associated with predates sequence = 10000 - sequence 1 might not yet even be committed for hours after sequence = 10000 was)

I don't view this as a problem, unless there is a bug in the developed code whereby a developer thought

a) they are gap free
b) they imply order

Just like you have a bug in your code if you think group by x,y returns data sorted by x,y - you need an order by for that.



sequence

A reader, December 15, 2009 - 7:25 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library