How do cursor objects work in python-oracledb?

Question

I'm rewriting a program in Python; the original program is written in C with embedded PL/SQL statements making calls to Oracle database.

In the original program, host variables are declared which are passed to Oracle and back, with their values being updated by Oracle stored procedures/functions. The program disconnects and reconnects to the database regularly, and when it does so all of these host variables maintain their values.

When recreating this in Python, I have to create in/out bind variables using python-oracledb's cursor.var() method. Whilst I've got these working fine, I don't understand what is actually happening under the hood, as a result I'm unsure how to approach cursor closing & connection handling in my Python script.

It appears that a cursor object is tied to a connection object.

So when I close and reopen the connection, I would assume that the variables tied to the cursor (created using cursor.var) would be lost. However I've tried this out and they appear to maintain their value. Why is this?

When I asked ChatGPT about this it said it shouldn't happen, but as ChatGPT is out of date (using cx_oracle instead) it appears this functionality is a new feature; I can't find anything on google to confirm this however. So my questions is, what exactly is happening when I call connection.cursor() and cursor.var(); what are these objects being created, and how are they affected by disconnecting and reconnecting to the database?

I'm a rookie software dev and haven't studied much computer science, so if these questions are due to a lack of background understanding then please could you point me in the direction of some resources that will help me better understand whats happening!

The test code I made to check if values were maintained is as follows:

import oracledb # allows python to talk to oracle
import connection_config # username and database id for connection

con = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor = con.cursor()   # connection cursor 

host_variable = cursor.var(str)
host_variable.setvalue(0,'VALUE')

print(host_variable.getvalue())

con.close()

con = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor = con.cursor()   # connection cursor 

print(host_variable.getvalue())

Both print statements print 'VALUE'. According to chatgpt this wouldn't happen with the old oracle driver, cx_oracle.

TIA!

Answer 1

Most of the types do not require an active connection to the database so they will remain viable indefinitely. This is not true, however, for LOB types, so you cannot count on the fact that the values in the variable remain viable after the connection on which they were created is closed.

Answer 2

A Python-OracleBD cursor variable is used to pass data into a bind parameter.

An Oracle cursor has a lifetime of the user's session and when you close the connection then you will close the session and all its related resources including cursors. When you open a second connection then it will have a second session.

You can still see the value because you are still looking at the python data structure representing a variable associated with the first cursor; you are not looking at anything to do with the second cursor.

Try looking at a different cursor variable for the second connection:

import oracledb # allows python to talk to oracle
import connection_config # username and database id for connection

con1 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor1 = con1.cursor()   # connection cursor 

host_variable1 = cursor1.var(str)
host_variable1.setvalue(0,'VALUE')

print(host_variable1.getvalue())

con1.close()

con2 = oracledb.connect(user=connection_config.user, password=connection_config.pw, dsn=connection_config.dsn)
cursor2 = con2.cursor()   # connection cursor 
host_variable2 = cursor2.var(str)

print(host_variable2.getvalue())

You will see that you are not looking at the same value.

I want the variable to maintain its value when the connection is reset, and the variable to be useable in queries with the new connection once its reset.

You CANNOT maintain the value in a cursor variable on the database because cursors only have a lifetime of your current session and when you have a different connection then you will have a different session (even for the same user).

(Apart from that, you have only instantiated a Python data structure that is used to pass the value to the database; you have not used the Python-OracleDB cursor object to execute any commands on the database so the value you have set only exists in the Python client application and has never been sent to the database server. Even if you did send it to the database you would need to do something extra with it to persist the value such as using an INSERT statement.)

You could:

• Keep the value in a Python variable on the client;
• Set a variable belonging to a package within the Oracle database;
• INSERT the value into a table.