Cancel a cursor.execute()

[sergiownd]

Hi, I'm running a query which takes a long time to finish. I want to be able to kill the connection with CTRL+C or kill PID. I've tried to use the connection.cancel() o the terminate process but the query keeps on executing. I've also tried to make the query on a subprocess but even if i kill the process the query is still executed. Is there any way of cancelling it?

Thanks.

[anthony-tuininga]

Can you provide some more information on platform, Oracle versions (database and client), cx_Oracle version. It would also be helpful if you can include the code you used so we can see if your use of cancel() is being done correctly! Thanks.

[sergiownd]

Here is the code:

import cx_Oracle
import sys
import signal

def signal_term_handler(signal, frame):
    connection.cancel()
    sys.exit('end')

ip = 'localhost'
port = 49161
SID = 'xe'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect('system', 'oracle', dsn_tns)
cursor = connection.cursor()

signal.signal(signal.SIGTERM, signal_term_handler)
signal.signal(signal.SIGINT, signal_term_handler)
cursor.execute("create table testear as select * from test union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3")


return()

I've also used cursor.close() on the signal process, declaring the cursor earlier, and nothing. The process stops but the query is still launched.
I've also tried stop it with multi process:

ip = 'localhost'
port = 49161
SID = 'xe'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect('system', 'oracle', dsn_tns)
cursor = connection.cursor()

def signal_term_handler(signal, frame):
    cursor.close()
    sys.exit('Excution stopped manually.')

def threat_treatment():
	cursor.execute("create table testear as select * from test union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3 union select * from test2 union select * from test3")


t1 = Process(target=threat_treatment)
t1.start()
signal.signal(signal.SIGTERM, signal_term_handler)
signal.signal(signal.SIGINT, signal_term_handler)
t1.join()

return(1)

I'm using the latest version of cx_oracle, and I'm user docker to prepare the database with oracle-xe-11g.

The connection with the database works good. The table is created, the problem is that I cannot cancel it.

Thank you for helping

[cjbj]

I have a feeling this is a duplicate of https://stackoverflow.com/questions/50019989/cancel-interrupt-a-query-execute-on-cx-oracle

[sergiownd]

Yes, actually It is my post too.

[cjbj]

Check the firewall or try DISABLE_OOB=on in a sqlnet.ora. And post platform and version details too, please!

[sergiownd]

Where should I add the DISABLE_OOB=on (I'm some kind of new to Oracle) Thanks. And about platform I dont know if u mean my machine or what else.

[cjbj]

This has some applicable info https://oracle.github.io/node-oracledb/doc/api.html#tnsadmin (the network layer is common with cx_Oracle)

Platform == whatever you are running on: operating system, virtual machines etc.

[sergiownd]

I'm running the code on a Ubuntu Budgie and the data base running on a docker (oracle-xe-11g)

[anthony-tuininga]

Closing due to lack of activity.

[vbvasu]

Hi,
Im trying to execute like 15-20 queries using cx_oracle python
Some of the queries may run long sometimes i am searching for a way to stop the query if its still running for more than 30 min i would like to skip this and cancel the execution and gopn executing other queries.
Is there anyway to track the time and cancel the query execution and skip this

[anthony-tuininga]

Take a look at the attribute call_timeout. This is for the replacement for cx_Oracle called python-oracledb but the same information applies to cx_Oracle.

[vbvasu]

I dont think it will help me resolving the issue.

[anthony-tuininga]

Why do you think that? Open a new issue with details!