1

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!

Improve this question

2 Answers 2

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks, but I'm not sure how this answers my question? 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. I'm not looking to create a new host variable, and also as the program is running on an infinite loop I need to use the same connection name each time
@SimonT I've updated the answer; however, it does answer your question because it would appear that your question is based on a flawed premise and cursor variables do not do what you seem to think that they do.
Thanks, this is helpful. I'm still not fully understanding what cursor variables do however (which is what I was asking to begin with!), could you clarify? If the cursor variable is just an instance of a class in python, what about it ties it to a specific connection? If I'm running the program as a loop and I just do a connection.close() then establish a new connection with the same name each time it loops, how does the variable know its not associated with the current connection?
@SimonT There might be some buffering in the cursor, which is independent of the database connection.
@Barmar The OP's code has opened a connection and created some python objects but has not executed any statements using the Python "cursor" object so there will not be any Oracle cursor created within the database (yet) and nothing has been returned from the database. So far in the OP's code (excluding opening the connections) the only values that have been set are attributes of Python objects local to the client.
|
2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.