ERROR REGARDING GETTING CURRENT VALUE FROM SEQUENCE

[2314]

hI,
I AM GETTING THE FOLLOWING ERROR WHEN I TRY TO GET CURRENT VALUE OF SEQUENCE EVEN THOUGH I HAVE ALREADY CREATED THE SEQUENCE AND AM USING IT IN INSERT

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

 

[SantaMufasa]

2314,

My crystal ball is a bit foggy right now. Could you please post a copy-and-paste of your session from the INSERT statement, down through the statement that receives the error?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 04:58 (18Nov04) UTC (aka "GMT" and "Zulu"),
@ 21:58 (17Nov04) Mountain Time

 

[2314]

what i mean is that when i try to get the current value of sequence without initally firing nextval i am getting the error if i fire nextval then there is no error

 

[SantaMufasa]

2314,

What you experienced is published behaviour of "currval" -- You cannot access it until after you have accessed "nextval". "Currval's" entire reason for living is to show you the value that the sequence most recently "delivered" to the session asking to see "currval". So what you see is what you should always expect to happen.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 05:24 (18Nov04) UTC (aka "GMT" and "Zulu"),
@ 22:24 (17Nov04) Mountain Time

 

[sem]

I think that the term SESSION should be emphasized. CURRVAL exists only on session level, i.e. each session has its own current value, but again, only after obtaining NEXTVAL.

Regards, Dima

http://seminihin.narod.ru

 

[2314]

CAN U GUYS EXPLAIN ME THE SESSION CONCEPT OF ORACLE THANKS IN ADVANCE

 

[SantaMufasa]

2314,

If I log into SQL*Plus in one window of my PC, then I log into SQL*Plus in another window of my PC, I have two sessions connected to my Oracle instance.

Before I can issue the command "SELECT my_sequence.currval FROM dual;" in Session A, I must first have had to issue an access of "nextval" similar to "SELECT my_sequence.nextval FROM dual;" from Session A. Session A (in Window A) is completely independent of Session B (in Window B). Each connection to the Oracle instance is a separate session.

Does this help at all?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 05:41 (19Nov04) UTC (aka "GMT" and "Zulu"),
@ 22:41 (18Nov04) Mountain Time

 

[2314]

thanks boss for clearing the concept

 

[weberm]

Mufasa wrote:

What you experienced is published behaviour of "currval" -- You cannot access it until after you have accessed "nextval". "Currval's" entire reason for living is to show you the value that the sequence most recently "delivered" to the session asking to see "currval". So what you see is what you should always expect to happen.

Wow, I was just about to ask about the very same problem! How does one obtain the CURRVAL without issuing NEXTVAL? When I examine the sequence in TOAD's schema browser, it shows a value and I'm pretty sure it's not using NEXTVAL.

 

[carp]

How does one obtain the CURRVAL without issuing NEXTVAL?
As explained by SantaMufasa, one doesn't!
Schema browser is probably pulling a value from LAST_NUMBER in the dba_sequences view. This is not the same thing as CURRVAL.

 

[SantaMufasa]

As usual, Carp is absolutely correct.

DBA_SEQUENCES.<sequence_name>.LAST_NUMBER contains for that sequence its:

The prevevious value of LAST_NUMBER + (CACHE_SIZE * INCREMENT_BY), then TOAD subtracts one "INCREMENT_BY", trying to "anticipate" CURVAL. Using that technique, however, TOAD's calculation might be inaccurate when the CACHE_SIZE > 1. So TOAD is doing the best it can under the circumstances, but don't be basing any business-critical decisions on TOAD's value if a sequence's CACHE_SIZE > 1.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:08 (24Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:08 (24Nov04) Mountain Time

 

[weberm]

Ahhh, so THAT'S how it's done! This will be just what I need to write some PL/SQL script which will detect if processing a set of receipts will cause the max_value to be exceded. Thank you!

 

[weberm]

OK, one more question...

I can run a SELECT statement against dba_sequences as a stand-alone script but when I put it into a stored function, I get a The following error has occurred:

[tt]FUNCTION RCPI.RECEIPT_SEQUENCE_IS_TOO_HIGH
On line: 12
PLS-00201: identifier 'SYS.DBA_SEQUENCES' must be declared[/tt]

Shouldn't this object have the same grants as it would if run as a stand-alone?

 

[carp]

Is access to dba_sequences granted explicitly to your account or via a role?

 

[SantaMufasa]

2314,

As an alternative to accessing "dba_sequences", you can access "all_sequences" or "user_sequences" for the same results without having to worry about permissions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:27 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 12:27 (06Dec04) Mountain Time

 

[weberm]

Using "all_sequences" or "user_sequences" seems to do the trick! Thank you for your sage advice.