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

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