How to receive a out parameter(sys_refcursor) of stored procedure Oracle in Django

Question

I have created a stored procedure usuarios_get , I test it in oracle console and work fine. This is the code of stored procedure

create or replace PROCEDURE USUARIOS_GET(
text_search     in VARCHAR2,
usuarios_list   out sys_refcursor
)  
AS
--Variables
BEGIN
open usuarios_list for select * from USUARIO
END USUARIOS_GET;

The python code is this:

with connection.cursor() as cursor:
    listado = cursor.var(cx_Oracle.CURSOR)
    l_query = cursor.callproc('usuarios_get', ('',listado))  #in this sentence produces error
    l_results = l_query[1]

The error is the following:

NotSupportedError: Variable_TypeByValue(): unhandled data type VariableWrapper

I've also tried with other stored procedure with a out parameter number type and modifying in python code listado= cursor.var(cx_Oracle.NUMBER) and I get the same error

NotSupportedError: Variable_TypeByValue(): unhandled data type VariableWrapper

I work with

• python 2.7.12
• Django 1.10.4
• cx_Oracle 5.2.1
• Oracle 12c

Can any help me with this?

Thanks

Answer 1

The problem is that Django's wrapper is incomplete. As such you need to make sure you have a "raw" cx_Oracle cursor instead. You can do that using the following code:

django_cursor = connection.cursor()
raw_cursor = django_cursor.connection.cursor()
out_arg = raw_cursor.var(int) # or raw_cursor.var(float)
raw_cursor.callproc("<procedure_name>", (in_arg, out_arg))
out_val = out_arg.getvalue()

Then use the "raw" cursor to create the variable and call the stored procedure.

Looking at the definition of the variable wrapper in Django it also looks like you can access the "var" property on the wrapper. You can also pass that directly to the stored procedure instead -- but I don't know if that is a better long-term option or not!