pyhon cx_Oracle 限制oracle调用执行命令时间

pyhon cx_Oracle 限制oracle调用执行命令时间

Oracle 使用场景：多线程批量远程执行sql命令，会在检查某一数据库时因为未知问题会卡住(网络或IO)，需要设置超时限制，超时之后只是某个主机的命令执行失败，不影响整个程序运行。

import cx_Oracle
from cx_Oracle import DatabaseError

my_username   = 'sentinel2'
my_password   = 'sentinel'
my_ip         = '1.1.1.91'
my_port       = '1521'
my_srvname    = 'pdb1'
my_connnection = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='+my_ip+')(PORT='+my_port+'))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME='+my_srvname+')))'


select_sql = """select 1 from dual"""
my_conn = cx_Oracle.connect(my_username, my_password, my_connnection)
# Oracle Client library 18.1版本以上输出为0，call_timeout 单位为ms
print(my_conn.call_timeout)
# 设置超时的时间之后，输出将显示为设置值 
my_conn.call_timeout=10*1000
print(my_conn.call_timeout)
my_cur = my_conn.cursor()

-- 查询
my_cur.execute(select_sql)
my_rst=my_cur.fetchall()
print("my_rst: ",my_rst)


-- 更新 使用行锁阻塞模拟执行时间长的SQL  
update_sql = """update cog.t1 set id=id+1 """
my_conn.call_timeout=10*1000
print(my_conn.call_timeout)
my_cur.execute(update_sql)
my_conn.commit()

my_cur.close()

在sqlplus开一个窗口，更新之后未提示，模拟SQL执行超过指定时，会报如下报错

>>> my_cur.execute(update_sql)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: DPI-1067: call timeout of 10000 ms exceeded with ORA-3156

参考：https://cx-oracle.readthedocs.io/en/latest/
官方对于 Connection.call_timeout 调用超时的解释：

This read-write attribute specifies the amount of time (in milliseconds) that a single round-trip to the database may take before a timeout will occur. A value of 0 means that no timeout will take place.

If a timeout occurs, the error DPI-1067 will be returned if the connection is still usable. Alternatively the error DPI-1080 will be returned if the connection has become invalid and can no longer be used.

New in version 7.0.
Changed in version 8.2: For consistency and compliance with the PEP 8 naming style, the attribute callTimeout was renamed to call_timeout. The old name will continue to work for a period of time. The error DPI-1080 was also introduced in this release.

Note: This attribute is an extension to the DB API definition and is only available in Oracle Client 18c and higher.

Oracle Client library版本不是18.1以上时报错

-- 使用当前最新版本的Cx_Oracle 8.2.1 python输出属性时有如下提示：需要Oracle Client library是18.1或以上
>>> print(my_conn.call_timeout)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: DPI-1050: Oracle Client library is at version 12.1 but version 18.1 or higher is needed

需要下载Oracle Client 19C以上版本进行安装
https://www.oracle.com/database/technologies/instant-client/downloads.html