python script is hanging while calling a procedure in database

[prasanthi417]

Need help in how to maintain database connection alive

My python script is connecting to Oracle DB and is calling a stored procedure( this procedure will deletes data from table).The time taken for deletion of records from the table is taking more than 2 hrs.

After that python script is not executing the next set of commands, it is getting hanged in calling the procedure

please help how to fix this

My code:

import cx_Oracle
conn_str = "username/password@host:port/service"
d_dbh = cx_Oracle.connect(conn_str)
d_cursor = d_dbh.cursor()
d_cursor.execute(procedure_name)
d_cursor.execute("select count(1) from table_name)
delete_count = d_cursor.fetchone()[0]
print(delete_count)

As per the logs, the python script is hanged at calling that procedure. verified the logs from database end the records got deleted from table after 2hrs. but after 2hr the python script did not execute the next select statements.

Please help if this a session time out?

Larz60+ write Jan-16-2024, 11:14 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Code modified for you this time. Please use BBCode tags on future posts.

[deanhystad]

Have you tried reconnecting?

[prasanthi417]

Have you tried reconnecting?

Before calling the procedure I am establishing conn to database

[sgrey]

- What interface are you using to manage the database? Does it hang or experience slowness?
- Did you try another language, does it also hangs or just python?
- Is your table large or did you indexes get dropped? Do you have dependencies in the data? Does this procedure execute properly if you do it through your DBMS interface?
- Have you look at the driver documentation, perhaps there is a known bug? Are you using the latest version? Is your driver version compatible with you python version?
- Check your connection string. Maybe there is something there that makes it behave weirdly. Try creating it in a different way.
- What OS are you running? Do you have a firewall/antivirus thing running? Perhaps they are killing your connection, try to disable them and see what happens.
- In my experience sometimes databases can work better if you "ping" them. Try to connect, and then disconnect and reconnect. Does it make any difference? How long does it take to connect?
- Is you DB hosted on your computer? Is it on a separate disk? Maybe that disk has issues/bad sectors/is dying. Check it's health.

[deanhystad]

Have you tried making a new connection to the database after calling the procedure? Maybe the database dropped the old connection after 2 hours of inactivity.