| Currval and disconnect [message #214822] |
Thu, 18 January 2007 03:58  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Hello
If I run the following script:
CONNECT 1 APP_USER DATABASE/APP_USER_PSW;
CREATE SEQUENCE APP_USER.SEQ1
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
DISCONNECT 1;
CONNECT 1 APP_USER DATABASE/APP_USER_PSW;
SELECT APP_USER.SEQ1.NEXTVAL FROM DUAL;
i see 1. ok.
SELECT APP_USER.SEQ1.CURRVAL FROM DUAL;
i see 1. ok.
DISCONNECT 1;
SELECT APP_USER.SEQ1.CURRVAL FROM DUAL;
i get error ORA-08002: sequence NAME.CURRVAL is not yet defined in this session. I see in http://www.techonthenet.com/oracle/errors/ora08002.php that the solution for that is to call
SELECT APP_USER.SEQ1.NEXTVAL FROM DUAL;
firstly and only then
SELECT APP_USER.SEQ1.CURRVAL FROM DUAL;
but in this case the counter is set to 2. I really need to get 1. I dont want disconnects and connects to influence my counter.
Is there another way to make the sequence visible for the session?
|
|
|
|
| Re: Currval and disconnect [message #214824 is a reply to message #214822] |
Thu, 18 January 2007 04:11  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
That is entirely as expected and is explained in the documentation. CURRVAL will only be available in the same session that a NEXTVAL was selected.
If you think it through, it's completely reasonable. Let's suppose you select NEXTVAL from a table, disconnect and then re-connect 5 hours later. How can Oracle have "remembered" the number you had from 5 hours earlier ? In the meantime, 50 other users may have logged in and all done "select nextval". So what is the value of currval now ? If it just takes the "current" value of the sequence (which may be difficult to compute anyway because there may be many users simultaneously picking numbers from it), it is not going to be the value you used earlier in the day.
[Updated on: Thu, 18 January 2007 04:12] Report message to a moderator |
|
|
|
| Re: Currval and disconnect [message #214835 is a reply to message #214824] |
Thu, 18 January 2007 04:30 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
"How can Oracle have "remembered" the number you had from 5 hours earlier ? In the meantime, 50 other users may have logged in and all done "select nextval"."
Oracle should remeber that just the same way it does it now. But if the user logs in and does CURRVAL, he should get 50 then, and not do NEXTVAL, then CURRVAL, and get 51.
"If it just takes the "current" value of the sequence (which may be difficult to compute anyway because there may be many users simultaneously picking numbers from it), it is not going to be the value you used earlier in the day."
Why more difficult than it is implemented now?
I dont need the value earlier that day. I need to know the current value of the sequence when I connect and not incrementing it.
[Updated on: Thu, 18 January 2007 04:33] Report message to a moderator |
|
|
|
| Re: Currval and disconnect [message #214846 is a reply to message #214822] |
Thu, 18 January 2007 04:39 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
You're not understanding what sequences are for. They are designed for OLTP systems in which lots of users are simultaneously creating records and need quickly to get a sequence number as a key for those records.
If you have a single user system, then you would be better off not using sequences and just have a sequence number table of your own. Then you could just update it when you have used the next value and to get the current value you would just have to keep querying it.
As to how Oracle can remember the number, that is because it is stored in the memory of your database session. Once you disconnect from the database, that is gone. So where is Oracle going to store it now ?
[Updated on: Thu, 18 January 2007 04:45] Report message to a moderator |
|
|
|
| Re: Currval and disconnect [message #214855 is a reply to message #214846] |
Thu, 18 January 2007 04:53 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Single user- i mean 1 Oracle user, but there can be many clients that connect with the same Oracle user.
And yes, I need the sequence for primary key columns.
But I have to fulfill some odd requirement, that all records in a table should contain no gaps. And user disconnection/connectionions create gaps in a table...
"So where is Oracle going to store it now ?"
It could load the sequence numbers to the session memory again when the user connects to Oracle.
[Updated on: Thu, 18 January 2007 04:54] Report message to a moderator |
|
|
|
| Re: Currval and disconnect [message #214859 is a reply to message #214822] |
Thu, 18 January 2007 05:04 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
The stuff about no gaps in the sequence is a hoary old chestnut that has been discussed to death in numerous forums. There is fundamentally no way of guaranteeing this and also having full multi-user access. You could probably minimize the number of lost values by setting the cache size to 1, but you will not be able to get rid of them altogether.
| Quote: | It could load the sequence numbers to the session memory again when the user connects to Oracle.
|
It could, but it doesn't. Partly this is because some systems user the same Oracle username to connect to the application. So, for example, Joe may connect as ORDER_SYSTEM from his PC and Fred may connect using the same username from a different PC. If they both disconnect and then Joe logs back on, how is it going to know that the sequence number it's got stored was his and not Fred's ?
|
|
|
|
| Re: Currval and disconnect [message #214863 is a reply to message #214859] |
Thu, 18 January 2007 05:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you set the cache_size on an index to 1, then you can see the last value selected be doingSELECT last_number FROM all_sequences WHERE sequence_name = <sequence>
|
|
|
|
| Re: Currval and disconnect [message #214864 is a reply to message #214863] |
Thu, 18 January 2007 05:18 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you want a gap free series of numbers, then Sequences aren't what you're looking for, as the explicitly don't guarantee to be gap free.
The best solution I've ever come across is to use a sequence for your primary key,and have the gap free column as another column on the table.
You have a sweeper process that runs every minute or so, and populates the gap free column on all the records that are in the database and definitely aren't going to get rolled back.
|
|
|
|
| Re: Currval and disconnect [message #214865 is a reply to message #214822] |
Thu, 18 January 2007 05:20 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
| Quote: | SELECT last_number FROM all_sequences WHERE sequence_name = <sequence>
|
I wouldn't want to rely on that. If you've got a genuine multi-user system, then users could be grabbing values from the sequence all the time and it could quickly change from what you originally had.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
