在cx_oracle中返回一个存储过程的游标变量

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

我正在尝试使用cx_oracle从python中的一个过程返回refcursor，我的过程如下所示。在这个过程的下面是我正在尝试使用的python。当我运行脚本时，所有返回的都是

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

如何遍历refcursor ?甲骨文的文档

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 script

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()

database oracle 12c client: 12.1.0.2

数据库oracle 12c客户端:12.1.0.2。

1 个解决方案

#1

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]

Then you can print the result with a for loop

然后可以用for循环打印结果

for line in ret_cursor:
    print line

or with print ret_cursor.fetchall() , or with the pprint tool if needed.

或者使用print ret_cursor.fetchall()，或者在需要时使用pprint工具。

In the documentation you've linked, the return value is directly unpacked to l_query and l_emp:

在您链接的文档中，返回值直接解压缩到l_query和l_emp:

l_query, l_emp = self.__cursor.callproc("PKG_HR.FIND_EMPLOYEES", [p_query, l_cur])

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 .

顺便说一下，您可能需要在末尾关闭返回的游标，方法与主游标相同:ret_cursor.close()。否则，它会抛出一个关于连接无法关闭的异常。

#1