CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
ben.reisinger
ben.reisinger

Reputation: 23

Sequence nextval/currval in two sessions

Setup: Oracle DB running on a windows machine

Mac connected with the database, both in the same network

Problem: When I created a sequence in SQL Developer, I can see and use the sequence in this session. If I logoff and login again the sequence is still there. But if I try to use the sequence via Python and cx_Oracle, it doesn't work. It also doesn't work the other way around.

[In SQL Developer: user: uc]

create SEQUENCE seq1;

select seq1.nextval from dual; ---> 1

commit; --> although the create statement is a DDL method, just in case

[login via Python, user: uc]

select seq1.currval from dual;--> ORA-08002 Sequence seq1.currval isn't defined in this session

The python code:

import cx_Oracle

cx_Oracle.init_oracle_client(lib_dir="/Users/benreisinger/Documents/testclients/instantclient_19_8", config_dir=None, error_url=None, driver_name=None)
# Connect as user "hr" with password "hr" to the "orclpdb" service running on a remote computer.
connection = cx_Oracle.connect("uc", "uc", "10.0.0.22/orcl")

cursor = connection.cursor()
cursor.execute("""
         select seq1.currval from dual
                """)
print(cursor)

for seq1 in cursor:
    print(seq1)

The error says, that [seq1] wasn't defined in this session, but why does the following work:

select seq1.nextval from dual --> returns 2

Even after issuing this, I can't use seq1.currval

Btw., select sequence_name from user_sequences returns seq1in Python

[as SYS user]

select * from v$session 
    where username = 'uc';

--> returns zero rows

Why is seq1 not in reach for the python program ?

Note: With tables, everything just works fine

EDIT: also with 'UC' being upper case, no rows returned

first issuing

still doesn't work

Upvotes: 0

Views: 1621

Answers (3)

Koen Lostrie
Koen Lostrie

Reputation: 18685

Not sure how to explain this. The previous 2 answers are correct, but somehow you seem to miss the point. First, take everything that is irrelevant out of the equation. Mac client on Windows db: doesn't matter. SQLDeveloper vs python: doesn't matter. The only thing that matters is that you connect twice to the database as the same schema. You connect twice, that means that you have 2 separate sessions and those sessions don't know about each other. Both sessions have access to the same database objects, so you if you execute ddl (eg create sequence), that object will be visible in the other session. Now to the core of your question. The oracle documentation states "To use or refer to the current sequence value of your session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (in the current or a previous transaction)."

You have 2 different sessions, so according to the documentation, you should not be able to call seq_name.CURRVAL in the other session. That is exactly the behaviour you are seeing.

You ask "Why is seq1 not in reach for the python program ?". The answer is: you're not correct, it is in reach for the python program. You can call seq1.NEXTVAL from any session. But you cannot invoke seq1.NEXTVAL from one session (SQLDeveloper) and then invoke seq1.CURRVAL from another session (python) because that is just how sequences works as stated in documentation.

Just to confirm you're not in the same session, execute the following statement for both clients (SQLDeveloper and python):

select sys_context('USERENV','SID') from dual;

You'll notice that the session id is different.

Upvotes: 2

APC
APC

Reputation: 146289

CURRVAL returns the last allocated sequence number in the current session. So it only works when we have previously executed a NEXTVAL. So these two statements will return the same value when run in the same session:

select seq1.nextval from dual
/
select seq1.currval from dual
/

It's not entirely clear what you're trying to achieve, but it looks like your python code is executing a single statement for the connection, so it's not tapping into an existing session.

This statement returns zero rows ...

select * from v$session 
    where username = 'uc';

... because database objects in Oracle are stored in UPPER case (at least by default, but it's wise to stick with that default. So use where username = 'UC' instead.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143003

Python established a new session. In it, sequence hasn't been invoked yet, so its currval doesn't exist. First you have to select nextval (which, as you said, returned 2) - only then currval will make sense.

Saying that

Even after issuing this, I can't use seq1.currval

is hard to believe.

This: select * From v$session where username = 'uc' returned nothing because - by default - all objects are stored in uppercase, so you should have ran

.... where username = 'UC'

Finally:

commit; --> although the create statement is a DDL method, just in case

Which case? There's no case. DDL commits. Moreover, commits twice (before and after the actual DDL statement). And there's nothing to commit either. Therefore, what you did is unnecessary and pretty much useless.

Upvotes: 0

Related Questions