Execution Issue with callproc() with IN parameters

[princyangelina]

Hello,

I have procedure created in Oracle database which looks similar to this sample -

create or replace PROCEDURE MyProcedure(
v_ID IN  MySchema.MyTable.MID%TYPE, 
v_DATA OUT SYS_REFCURSOR)

AS
BEGIN

OPEN v_DATA FOR 
  SELECT * FROM MySchema.MyTable om
  WHERE  om.MID = v_ID
  ORDER BY om.MID DESC;

END;

When I executed this code, it kept running without any errors or results being returned -

import oracledb

dsn = oracledb.makedsn(host=**, port=**, service_name=**)
con = oracledb.connect(user=**, password=**, dsn=dsn)
cur = con.cursor()

out_var = cur.var(oracledb.CURSOR)
res = cur.callproc("MySchema.MyProcedure", [5, out_var])

cur.close()
con.close()

Interestingly, callproc() worked fine when I removed the IN parameter from the procedure and just kept the OUT parameter. It returned the all the records of MyTable without any problem. This problem comes up if I have any IN parameter in the procedure.

I have tried all these options that I could find on the internet but none of them worked for me

• providing the keyword_parameters instead of the parameters
• using execute() instead of callproc() with this statement - "begin MySchema.MyProcedure(:1, :2, :3); end;", [5, cur.var(oracledb.CURSOR)])

Let me know if you need more information. Thank you in advance!

[anthony-tuininga]

Can you supply an actual complete example that demonstrates the problem? Include the SQL that you used to create the procedure and the table and the insert statements to populate that table. Note that Test case 1301 in the test suite does pretty much exactly what you are doing and it works fine!

[princyangelina]

Hi @anthony-tuininga , apologize for the late response. I can't be sharing the actual code but the sample that I've shared exactly represents the actual one.

Surprisingly, we were able to resolve this issue by keeping OUT parameters first and then the IN parameters, both in the Database procedure and in Python. Not sure how keeping the IN parameters first failed but this approach worked.

[anthony-tuininga]

If you can supply an example that demonstrates the failure I'll take a look at it; otherwise, I can't really help you as the test suite demonstrates that the order of parameters does not appear to be important. It seems you have found a solution that works for you anyway!