python连接oracle数据库返回游标,在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

DB: 0.00400018692017 seconds

Total: 0.00400018692017 seconds

<__builtin__.oraclecursor on 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 script

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

解决方案

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

#>>>

So you can access the returned cursor by index :

ret_cursor = self.__cursor.callproc("prc_get_some_data",[l_cur])[0]

or

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 line in ret_cursor:

print line

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

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

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 .