Unable to UPDATE or delete from table. SELECT works fine.

[AQGX]

1. What versions are you using?

   cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (12, 1, 0, 1, 0)

   platform.platform: Windows-10-10.0.17763-SP0 sys.maxsize > 2**32: True platform.python_version: 3.7.0

2. Describe the problem

a. From the Python program we are able to SELECT from the table but not able to UPDATE or DELETE the table. b. From any SQL tool (Golden Benthic), we are able to SELECT, UPDATE, DELETE or any other oracle functions on the same table without any hassle.

3. Include a runnable Python script that shows the problem.

   
   # myscript.py
   
import cx_Oracle

cx_Oracle.connect('xxxx/xxxx@1.1.1.1/xxxx')

 cursor = connection.cursor()
 DLNO = 93
print(connection.version)
 command = "DLNOS =" + str(DLNO)



+ str(93)
print(command)


cursor.execute("DELETE FROM relaycmnds where SIGNO=1")


cursor.execute("Update relaycmnds set status = 2022 where DLNO = 93")


#cursor.execute("insert into relaycmnds values (1, 128, to_date('07/06/2022','MM/DD/YYYY'), 2022, 1, 93)")
#cursor.execute("insert into relaycmnds values (1, 128, 1032037760, 2022, 0, 93)") 

# cursor.execute("UPDATE relaycmnds SET STATUS = 200, UPDATESTATUS = 0 WHERE DLNO = 93") 
connection.commit()
 print('test1')
 cursor.close() 
connection.close()
 print('test2')



print(cursor)

[cjbj]

It works for us (and many other people). Check you are connecting to the correct database and user. Check out examples like https://github.com/oracle/python-cx_Oracle/blob/main/samples/bind_insert.py Explain what behavior you see.

[AQGX]

Thanks for the examples @cjbj On the behaviour part It just hangs, do not return any error code. We have to stop it manually to exit the run. In the case of Select, it works perfectly with a success message.

[cjbj]

If it hangs, then the bet is that you tried the same statement in another SQL tool but didn't commit, so the row is temporarily locked. Issue a COMMIT or ROLLBACK in that other tool.

[AQGX]

Thank you @cjbj You were spot-on. We killed the locked sessions and updated our code with Commit. It is working well now.

[anthony-tuininga]

Excellent! Thanks for following up.