问题描述
我正在尝试使用cx_oracle从python中的过程返回refcursor,我的过程看起来类似于下面的过程。该过程下面是我尝试使用的python。当我运行脚本时,返回的全部是
I am trying to return a refcursor from a procedure in python using cx_oracle, my procedure looks something like the one below. below the procedure is the python that I am trying to use. when I run the script, all that is returned is
DB: 0.00400018692017 seconds
Total: 0.00400018692017 seconds
<__builtin__.OracleCursor on <cx_Oracle.Connection to connection_string>>
我如何遍历反射器?
how can i iterate through the refcursor ?oracle documentation
PROCEDURE prc_get_some_data(
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('Python Script');
OPEN p_cursor FOR
SELECT *
FROM table;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END prc_get_somedatas;
python脚本
import sys, time
from datetime import date, timedelta
import random
import cx_Oracle
import string
# Python Script to call a PL/SQL stored procedure to bulk load data
class Test:
def __init__(self):
self.__db = cx_Oracle.connect('connection_string')
self.__cursor = self.__db.cursor()
def __exit__(self, type, value, traceback):
self.__cursor.close()
self.__db.close()
def get_some_data(self):
d=date.today() # Initialize a date variable with date of today
db_start = time.time()
start_all = time.time()
self.__cursor.bindarraysize = 2500
self.__cursor.arraysize = 10000
# create a cursor variable to return the results into
l_cur = self.__cursor.var(cx_Oracle.CURSOR)
#execute the procedure
l_test = self.__cursor.callproc("prc_get_some_data",[l_cur])
db_elapsed = (time.time() - db_start)
total_elapsed = (time.time() - start_all)
print "DB:\t ", db_elapsed, " seconds"
print "Total:\t ", total_elapsed, " seconds"
return list(l_test)
if __name__ == "__main__":
test = Test()
print test.get_some_data()
数据库oracle 12c
客户端:12.1.0.2
database oracle 12cclient: 12.1.0.2
推荐答案
对过程的调用将序列作为参数,并返回一个序列。
The call to a procedure takes a sequence as parameter, and returns a sequence too.
l_test = self.__cursor.callproc("prc_get_some_data",[l_cur])
print(type(l_test))
#>>> <class 'list'>
因此您可以按索引访问返回的游标:
So you can access the returned cursor by index :
ret_cursor = self.__cursor.callproc("prc_get_some_data",[l_cur])[0]
或
l_test = self.__cursor.callproc("prc_get_some_data",[l_cur])
ret_cursor = l_test[0]
然后可以使用for打印结果循环
Then you can print the result with a for loop
for line in ret_cursor:
print line
或使用 print ret_cursor.fetchall()
或使用 pprint
工具。
在(已链接),返回值直接解包到 l_query
和 l_emp
:
In the documentation you've linked, the return value is directly unpacked to l_query
and l_emp
:
l_query, l_emp = self.__cursor.callproc("PKG_HR.FIND_EMPLOYEES", [p_query, l_cur])
顺便说一句,您可能需要关闭返回的光标的结尾,使用与主光标相同的方法: ret_cursor.close()
。否则会引发无法关闭连接的异常。
By the way, you may need to close the returned cursor at the end, with the same method as the main cursor : ret_cursor.close()
. Otherwise it can throw an exception about the connection cannot be closed .
这篇关于在cx_oracle中将存储过程返回游标变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!