Error when call a procedure that has a ref_cursor as out parameter

[skysegbr]

When I call the proc using strings it works: result = cur.var(oracledb.CURSOR) cur.callproc('MANAGEMENT.RET_EMAIL_RECEIVER', ['23', '1', '1', '1', result])

but when I send using integer it got stuck: cur.callproc('MANAGEMENT.RET_EMAIL_RECEIVER', [23, 1, 1, 1 , result])

The procedure expects a number in parameter. Best regards.

Originally posted by @skysegbr in https://github.com/oracle/python-oracledb/discussions/167

[cjbj]

@skysegbr What is the error? Can you post the CREATE PROCEDURE statement?

The following works fine for me:

        cursor.execute("""
            create or replace procedure RET_EMAIL_RECEIVER (
                p1 in number,
                p2 in number,
                p3 in number,
                p4 in number,
                p5 out SYS_REFCURSOR) as
            begin
              open p5 for select p1,p2,p3,p4 from dual;
            end;
            """)

        result = connection.cursor()
        cursor.callproc('RET_EMAIL_RECEIVER', ['23', '1', '1', '1', result])
        for row in result:
            print(row)

        cursor.callproc('RET_EMAIL_RECEIVER', [45, 2, 2, 2, result])
        for row in result:
            print(row)

Output is:

$ python3 issue169.py 
(23, 1, 1, 1)
(45, 2, 2, 2)

[skysegbr]

In my case, I Try to call a procedure in a package. Package "MANAGEMENT". Procedure "RET_EMAIL_RECEIVER" I also tested with anonymous block.

it got stuck: sql = """ BEGIN MANAGEMENT.RET_EMAIL_DESTINATARIO( P_COD_FARM => :cod_farm, P_COD_FILI => :cod_fili, P_COD_REGRA => :cod_regra, P_COD_MENSAGEM => :cod_msg, P_CURSOR => :v_cursor ); END; """

cur = connection.cursor() result = cur.var(oracledb.CURSOR)

bind = { 'cod_farm': 23, 'cod_fili': 1, 'cod_regra': 1, 'cod_msg': 1, 'v_cursor': result }

cur.execute(sql, bind)

But if I put only one parameter of type str, it works fine.

sql = """ DECLARE V_COD_FARM NUMBER; V_COD_FILI NUMBER; V_COD_REGRA NUMBER; V_COD_MSG NUMBER; V_CURSOR SYS_REFCURSOR; V_TRASH VARCHAR2(2); BEGIN V_COD_FARM := :cod_farm; V_COD_FILI := :cod_fili; V_COD_REGRA:= :cod_regra; V_COD_MSG := :cod_msg; V_TRASH := :trash; V_CURSOR := :v_cursor;

GESTAO_PBM.RET_EMAIL_DESTINATARIO( P_COD_FARM => V_COD_FARM, P_COD_FILI => V_COD_FILI, P_COD_REGRA => V_COD_REGRA, P_COD_MENSAGEM => V_COD_MSG, P_CURSOR => V_CURSOR ); END; """

cur = connection.cursor() result = cur.var(oracledb.CURSOR)

bind = { 'cod_farm': 23, 'cod_fili': 1, 'cod_regra': 1, 'cod_msg': 1, 'trash': '1', 'v_cursor': result `}

cur.execute(sql, bind)

[cjbj]

What's the error?

And try result = connection.cursor() instead of result = cur.var(oracledb.CURSOR)

[skysegbr]

Hi, thanks for replying. It is not a function, the return of the procedure is through the output parameter of type SYS_REFCURSOR (oracle db.CURSOR). The method I am using is correct. The error occurs when I pass a number in the input parameter, when I call the procedure with numbers it crashes and does nothing, having to be canceled forcefully, and when I pass the parameters as a string, then it works ok.

[cjbj]

You'll need to supply us a testcase including the CREATE PROCEDURE call.

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[anthony-tuininga]

Closing - no further information provided