Named Parameters with a Oracle stored procedure in Python

Question

Trying to call an existing stored procedure, but using named parameters, first parameter should retain default value (in this case NULL).

I've spent way too long trying to get this working, any ideas?

create or replace procedure so_test(p1 in varchar2 default null, p2 in     varchar2  default null, p3 in varchar2  default null) as
begin
    null;
end;

import cx_Oracle
db = cx_Oracle.connect('/@XTS_DEV.CLIENT')
cur = db.cursor()
cur.callproc("so_test", ('X', 'Y', 'Z'))
cur.callproc("so_test(p2=>:1, p3=>:2)", ('Y', 'Z'))

Traceback (most recent call last):
  File "C:\Users\PatrickJolliffe\so_test.py", line 5, in <module>
    cur.callproc("so_test(p2=>:1, p3=>:2)", ('Y', 'Z'))
cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Anthony Tuininga · Accepted Answer · 2016-11-14 16:05:28Z

5

The documentation (http://cx-oracle.readthedocs.io/en/latest/cursor.html#Cursor.callproc) covers this but since no examples are provided there I can see how that might be confusing. :-)

For your example, this is what you want:

cursor.callproc("so_test", keywordParameters = dict(p2 = "Y", p3 = "Z"))

answered Nov 14, 2016 at 16:05

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anirban Nag 'tintinmj' Over a year ago

How would this return, with a list of input and output in the same order as procedure?

Collectives™ on Stack Overflow

Named Parameters with a Oracle stored procedure in Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related