Skip to Main Content
  • Questions

  • ORA-08002 error while select currval from a sequence

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, RAVINDAR.

Asked: June 06, 2000 - 1:27 am UTC

Last updated: January 23, 2005 - 11:33 am UTC

Version: 7.3.4

Viewed 10K+ times! This question is

You Asked

I want to select the current value from a sequence.

When i do : select <seq.name>.currval from dual

ORA-08002 error is appearing.
sequence %s.CURRVAL is not yet defined in this session

How to avoid this..

If i do : select <>.nextval from dual, then the value is
changed from the sequence. After that the currval is working.
But if i disconnect that session and again connect to that
session i am facing the error.

Other ways (Workaround ..):

1) i can take the value from dba_sequences (last_number-1).

2) I tried to alter the sequence with nocache. Eventhough
it is not working..

Can u help me to solve this issue.

Awaiting for your valuable suggesstion.

Thanking you

With best regards
Ravindar S.


and Tom said...

Currval is by definition the value returned by your sessions last call to nextval. If your session has not called Nextval yet, Currval is undefined.

The value in dba_sequences will typically not suffice to find what the nextval would be if you selected it as the last_number in dba_sequences jumps by the cache size. If you set the NOCACHE option (not recommended, it'll just make things slow), then last_number will return what nextval would have returned had you invoked it.

Since on an active system, whatever you get from dba_sequences is "stale" or out of date almost as soon as you get it (others will be selecting nextval all of the time) what I recommend is that you use either

last_number
last_number-cache_size+1

from dba_sequences. Either is a good approximation of what the Nextval would be (and all options to you are approximiations)...



Rating

  (10 ratings)

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

Comments

Getting NextVal from Dynamic Performance View

Pablo Rovedo, November 17, 2002 - 12:55 pm UTC

Tom:

You can get the nextval through the dynamic performance
view sys.v$_sequences.

Connecting with sys

sys@ROP816> desc v$_sequences
Name Null? Type
----------------------------------------------------- -------- ------------------------------
SEQUENCE_OWNER VARCHAR2(64)
SEQUENCE_NAME VARCHAR2(1000)
OBJECT# NUMBER
ACTIVE_FLAG VARCHAR2(1)
REPLENISH_FLAG VARCHAR2(1)
WRAP_FLAG VARCHAR2(1)
NEXTVALUE NUMBER
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NUMBER
HIGHWATER NUMBER
BACKGROUND_INSTANCE_LOCK VARCHAR2(1)
INSTANCE_LOCK_FLAGS NUMBER

sys@ROP816> create view v$sequences as select * from v$_sequences;

View created.

sys@ROP816> create public synonym v$sequences for sys.v$sequences;

Synonym created.

sys@ROP816> grant select on v$sequences to public;

Grant succeeded.

sys@ROP816>

and then connect with any user

rop@ROP816> create sequence s1_seq;

Sequence created.

rop@ROP816> select s1_seq.nextval from dual;

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

rop@ROP816> select b.object_name,a.nextvalue
2 from v$sequences a, user_objects b
3 where a.object# = b.object_id and
4 b.object_name = 'S1_SEQ'
5 /

OBJECT_NAME NEXTVALUE
-------------------- ----------
S1_SEQ 2

rop@ROP816> select s1_seq.nextval from dual;

NEXTVAL
----------
2

rop@ROP816> select b.object_name,a.nextvalue
2 from v$sequences a, user_objects b
3 where a.object# = b.object_id and
4 b.object_name = 'S1_SEQ'
5 /

OBJECT_NAME NEXTVALUE
-------------------- ----------
S1_SEQ 3

rop@ROP816>

Regards
Pablo A. Rovedo

PD: I have read your book twice, is excellent!!!
Are you writing a new one? any book regarding
performance would by great

Where is it stored?

VA, January 21, 2005 - 9:33 pm UTC

"Currval is by definition the value returned by your sessions last call to nextval"

Session 1:
create sequence s;
select s.nextval from dual; -- gives 1

Session 2:
declare x number;
begin
for i in 1..1000 loop
select s.nextval into x from dual;
end loop;
end;
/

Session 1:
select s.currval from dual; -- gives 1

So obviously, the 'currval' is stored in the session's pga/uga memory and not in the data dictionary.

That being the case, selecting currval into a local variable l_nextval and using that 1000s of times in that session would be the same as calling s.currval, right?

Thanks

Tom Kyte
January 22, 2005 - 9:48 am UTC

correct. it would not only be the "same", it would be at least 1000's times more efficient :)

Same with USER for example.

VA, January 22, 2005 - 10:49 am UTC

"it would not only be the "same", it would be at least 1000's times more efficient :)"

I am confused. Which one would be 1000s times more efficient? l_currval or s.currval? Why? They are both coming from the session's state/memory so why would one be more efficient than the other?

Tom Kyte
January 22, 2005 - 10:51 am UTC

l_currval.

why bug the database over and over again to retrieve something when you already have it.

currval comes from "dual" -- not in plsq, a context switch to the SERVER.

l_currval, that is just a variable in plsql.

Fast dual

VA, January 22, 2005 - 10:56 am UTC

Right, but that no longer holds true in 10g, right? As per your comments in

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1562813956388#19020485993865 <code>

So in 10g, select constant into l_variable from dual is the same as l_variable := constant, right?

Thanks

Tom Kyte
January 22, 2005 - 4:18 pm UTC 

no, not true.

"It need not goto the database data *at all*."

it removes the need to actually do a full scan, but it is still SQL.  It still context switches from plsql to sql.

ops$tkyte@ORA10G> drop sequence s;
Sequence dropped.
 
ops$tkyte@ORA10G> create sequence s;
Sequence created.
 

ops$tkyte@ORA10G> select s.nextval from dual;
 
   NEXTVAL
----------
         1
 
ops$tkyte@ORA10G> exec runStats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> declare
  2          l_s_currval number;
  3          l_var       number;
  4  begin
  5          select s.currval into l_s_currval from dual;
  6          for i in 1 .. 100000
  7          loop
  8                  l_var := l_s_currval;
  9          end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec runStats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> declare
  2          l_var       number;
  3  begin
  4          for i in 1 .. 100000
  5          loop
  6                  select s.currval into l_var from dual;
  7          end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec runStats_pkg.rs_stop(100)
Run1 ran in 2 hsecs
Run2 ran in 335 hsecs
run 1 ran in .6% of the time

<b>so, less wall clock and</b>

 
Name                                  Run1        Run2        Diff
LATCH.cache buffers chains           3,614       3,471        -143
STAT...recursive cpu usage               3         258         255
STAT...CPU used when call star           7         322         315
STAT...CPU used by this sessio           7         322         315
STAT...DB time                          10         337         327
STAT...Elapsed Time                      4         340         336
STAT...undo change vector size      47,632      46,308      -1,324
STAT...redo size                   100,504      95,744      -4,760
STAT...recursive calls                 105     100,005      99,900
LATCH.shared pool                      266     100,227      99,961
STAT...execute count                    17     100,005      99,988
STAT...calls to get snapshot s           8     100,001      99,993
STAT...session pga memory                0     131,072     131,072
LATCH.library cache                    249     200,140     199,891
LATCH.library cache pin                141     200,066     199,925
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
4,627     504,310     499,683       .92%
 
PL/SQL procedure successfully completed.

<b>lots less latching...

In 9i, it might look like the following</b>


ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(100)
Run1 ran in 11 hsecs
Run2 ran in 425 hsecs
run 1 ran in 2.59% of the time
 
Name                                  Run1        Run2        Diff
LATCH.simulator hash latch             135           0        -135
STAT...recursive cpu usage               1         342         341
STAT...CPU used by this sessio           2         407         405
STAT...CPU used when call star           2         407         405
STAT...Elapsed Time                     17         429         412
STAT...redo size                    70,104      65,008      -5,096
STAT...session pga memory max        8,524           0      -8,524
STAT...session pga memory                0      65,536      65,536
STAT...recursive calls                 111     100,004      99,893
LATCH.shared pool                      246     100,220      99,974
STAT...execute count                    18     100,005      99,987
STAT...buffer is not pinned co           4     100,000      99,996
STAT...no work - consistent re           4     100,000      99,996<b>
STAT...table scan blocks gotte           1     100,000      99,999
STAT...table scan rows gotten            1     100,000      99,999
STAT...table scans (short tabl           1     100,000      99,999</b>
LATCH.library cache pin                189     200,118     199,929
LATCH.library cache                    355     200,285     199,930
STAT...session logical reads           582     300,523     299,941
STAT...consistent gets                  26     300,003     299,977
STAT...calls to get snapshot s          19     300,001     299,982<b>
LATCH.cache buffers chains           2,734     602,621     599,887</b>
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
4,418   1,104,108   1,099,690       .40%
 
PL/SQL procedure successfully completed.

<b>the stuff in bold shows the actual IO's performed against dual...</b>

Alberto Dell'Era, January 22, 2005 - 3:37 pm UTC

> So in 10g, select constant into l_variable from dual is the same as
> l_variable := constant, right?

No, because FAST DUAL is a sql-engine optimization, not a
pl/sql one - so the "select constant from dual" statement
is sent to the sql-engine and *there* optimized to avoid
accessing dual, but you pay at least the cost of switching
from the pl/sql engine and the sql one (which is not cheap).

All it takes is to run a simple test (10.1.0.3):

dellera@ORACLE10> set timing on

dellera@ORACLE10> declare
2 l_n number;
3 begin
4 for i in 1..10000000 loop
5 l_n := 1;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.46

dellera@ORACLE10> declare
2 l_n number;
3 begin
4 for i in 1..10000000 loop
5 select 1 into l_n from dual;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:04:32.88

The elapsed time is "a bit" different ...

Moreover, the 10g pl/sql parser is an *optimizing* compiler,
so i would expect to optimize the first as:

---

for i in 1..10000000 loop
l_n := 1; <-- hey look, a loop invariant, let's rewrite ..
end loop;

---

l_n := 1;
for i in 1..10000000 loop <-- hey look, an empty loop, let's rewrite ..
end loop;

---

l_n := 1

---

Don't know if it's happening in this case, but if not, it may surely
happen in a next refinement (it's a standard code optimization technique).

Writing the "select ... from dual" would prevent this reasoning,
since the pl/sql optimizing compiler would see only a "sql statement
returning a number" that may change between a loop and the next.

Dissapointed

VA, January 22, 2005 - 4:44 pm UTC

"it removes the need to actually do a full scan, but it is still SQL. It still context switches from plsql to sql"

Hm, a little surprised and dissapointed.

Oracle knows that DUAL is a one-row table. Selecting anything from that table (except DUMMY) into a PL/SQL variable neednt do a context switch to SQL engine at all! The PL/SQL compiler can convert that to a simple assignment, cant it?

Tom Kyte
January 22, 2005 - 5:46 pm UTC

umm, well -- currval comes from the SQL side, nextval from the SQL side, sysdate from the SQL side -- you gotta get to the sql side to get them and you wouldn't use DUAL to get anything you DIDN'T need to go to the sql side to get would you?

so, it is about as good as it gets.

A reader, January 22, 2005 - 6:32 pm UTC

As always, you are not willing to concede that Oracle has any room for improvement, ever. :) "It is what it is, deal with it"!

Forget about sequences for a minute, all I am saying is that even if I do use DUAL to do a simple PL/SQL variable assignment that really doesnt need to go to dual, as Alberto pointed out, the PL/SQL compiler can easily do a optimization to not do the context switch and transform statement to a variable assignment

i.e. if I do
select 1 into l_variable from dual;

Oracle can internally rewrite it to

l_variable := 1;

This is what compilers should be all about, optimizing stuff, rewriting!

[I dont quite agree with Alberto's example of

for i in 1..10000000 loop <-- hey look, an empty loop, let's rewrite ..
end loop;

i.e. if I have
for i in 1..10000000 loop
null;
end loop;

I wouldnt want the compiler to simple ignore the loop because it is "empty", I might have put it in there for a reason, to intentionally introduce a delay]

But there is absolutely no reason Oracle cannot rewrite a scalar select from DUAL to a simple variable assignment!

Thanks

Tom Kyte
January 23, 2005 - 9:56 am UTC

no, it is that DUAL is "sql"

anything -- ANYTHING -- that truly and utterly needs DUAL (and you would never use dual to assign say the result of 5+4 to a variable would you?) needs to go to SQL.


If you did, you -- as the developer -- have sort of done something "not too smart"

Similar to not using binds, or parsing a statement 500000 times to execute it 500000 times.

You see -- it is sql, we need to go to the sql engine in order to do sql. You told us "hey, do this sql".


<quote>
I wouldnt want the compiler to simple ignore the loop because it is "empty", I
might have put it in there for a reason, to intentionally introduce a delay]
</quote>

that would be wrong on your part -- you would use dbms_lock.sleep for that. many compilers would in fact optimize that loop right out of existence.


But -- following your logic - you actually just outlawed us from change a query again dual. Perhaps you put it in there intentionally to make the code go slower (actually, to select 1 into var from dual -- there can be no other reason you did it that way -- it is your intention to make the code go slower).

It is SQL (and I have on many occassions said there is "room for improvement" -- don't know where you are coming from with that comment. but anyway)


There is absolutely no reason why one would do a scalar select from dual to perform a simple variable assignment!

Alberto Dell'Era, January 23, 2005 - 5:39 am UTC

>as Alberto pointed out, the PL/SQL compiler can easily do a optimization

Ahem, I pointed out that the PL/SQL compiler doesn't do, not that it could "easily" do it ;) ... in fact i think that doing that it's not easy at all, since it would need rewriting the compiler in order to let it "peek" at the sql statement hunting for "select from dual", thus fundamentally changing its architecture that, so far, has neatly separated the pl/sql and sql engine. Everything could be done, but since hopefully very few people select constants from dual, the benefit would not justify the cost (imho).

Especially since optimizing compilers are, in general, incredibly more complex than "regular" ones, so inherently a lot more buggy - i used to be a C programmer and i remember some bugs in a very, very mature C compiler with optimization flags on. The secret to stability is to control the complexity, thus introducing only the optimizations that really pay off.

>I wouldnt want the compiler to simple ignore the loop because it is "empty", I
>might have put it in there for a reason, to intentionally introduce a delay

A delay that can be 1 minute on my machine and near zero on another, so by zeroing the delay the compiler honours the contract with you ;)

BTW, this one is probably the most important optimization technique, implemented by ALL optimizing compilers for C, C++, Fortran, whatever, that i know about - "delay by looping" doesn't work in any language.

Tom Kyte
January 23, 2005 - 10:10 am UTC 

yes, and one more word on this actually..


create table dual ( x int );


is still valid *SQL* in 10g.

ops$tkyte@ORA10G> create table dual ( x int );
Table created.

so there is the fact that turning select 1 into var from dual into var := 1 would actually be "a bug", in the event someone was bold enough to do so....


the delay by looping just reminded me of my first computers.  People writing games back then used to use loops as delays.  When I got my first 286 machine, we had to CLOCK DOWN because all of the games that were written for the 4.77mhz 8088 broke by not using a timer to delay -- the new machines clocking at 12 and 16mhz were just too fast ;)

A reader, January 23, 2005 - 10:22 am UTC

I guess the discussion about the looping kind of took away from my main point.

Yes, I realize selecting from dual to do a variable assignment is stupid. But the job of compilers is to optimize away stupid stuff as long as the end-result is the same.

Tom, your example about someone creating their own DUAL table is not really applicable here. AS part of soft/hard parsing, Oracle knows that "dual" is either the real McCoy (sys.dual) or some user's fake dual. The compiler *could* apply the optimization I am suggesting to only the real dual table.

"But -- following your logic - you actually just outlawed us from change a query again dual. Perhaps you put it in there intentionally to make the code go slower (actually, to select 1 into var from dual -- there can be no other reason
you did it that way -- it is your intention to make the code go slower)"

Not really. All I saying is that the compiler should (could) rewrite to produce the same end-result, same in all respects. So, ignoring a empty loop would NOT apply here. I dont know of any compiler in any language that would just ignore a empty loop in this fashion!

But scalar select from dual is 100% equivalent to a variable assignment, the compiler should rewrite it.

Oh well, I guess everyone has said all there is to say about this, thanks for the discussion.

Thanks

[Tom, if you are still with me, can you point me to instances where you pointed out "room for improvement"? i.e. Oracle does things this way, I wish it would do it this way instead. I really havent seen any?]

Tom Kyte
January 23, 2005 - 10:52 am UTC

no, you are missing the point -- it is applicable.

dual is just a table.

dual is just a table.

dual is in fact, just a table.

this is just getting to the point of "silly"

You don't want the comiler to optimize out an empty loop, but you do want it to optimize out something that could potentially change the behaviour of the program? Hmm.

Think invokers rights, does the compiler *know* what dual will be used at compile time? (nope).



Everytime I point someone to metalink to file an enhancment request.

Everytime I file a bug for you once I give a simple test case.

Everytime I clarify the documentation and file a bug to get it better.

here is one that I just reread because someone followed up:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:17989406187750 <code>
includes the heretical line:
(i don't think i like this "fix").
for example.

You haven't heard me rant on about "i wish we didn't have autonomous transactions", or "when others"?

True -- I have a knack for pointing out the bright side of things -- by showing the way to do it in Oracle properly (which often differs from the sqlserver way -- but that is a two way street though, the way it is done in Oracle fails pretty badly in sqlserver and vice versa). But that should not be misconstrued as "defender of the Oracle". It is about education, not defense.

I don't say things I don't believe personally.

A reader, January 23, 2005 - 11:33 am UTC

OK fair enough, I got it now. Didnt think about invokers rights where the definition of a table is not fixed at compile-time.

Sorry for testing your patience!

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.