Problem with executing Oracle procedure by Python

Question

I would like to execute an Oracle procedure in Python. The procedure on the SQL side is well written. In the Python code, it receives errors: ORA-06550 and PLS-00306 - DatabaseError.

con = cx_Oracle.connect('***********')
cur = con.cursor()
faktura= "QQ0009365/21"
numer_listu = "421435636356536"
kod_k = int(112377)
out_data=str()
t = cur.callproc('QQ.ECOMMERCE.ZMIANA_OPISU_LIST_PRZEWOZOWY',[numer_listu,kod_k,faktura,out_data])

The function SQL Oracle looks like this:

QQ.ECOMMERCE.ZMIANA_OPISU_LIST_PRZEWOZOWY(P_NUMER_LISTU => :P_NUMER_LISTU,
                                                         P_KOD_KURIERA => :P_KOD_KURIERA,
                                                         P_NUMER_FAKTURY => :P_NUMER_FAKTURY,
                                                         P_ERROR_INFO => :P_ERROR_INFO);

is any of those parameters in the procedure an output parameter ?? , like p_error_info — Roberto Hernandez
– Roberto Hernandez, Commented Sep 3, 2021 at 11:57
the string parameters shouldn't be with single quotes, rather than double quotes, as some examples here --> blogs.oracle.com/oraclemagazine/… . I guess you have after that a variable to get the out return , right ? I have always used single quotes for the strings I pass to varchar2 variables in Oracle PL using callproc. — Roberto Hernandez
– Roberto Hernandez, Commented Sep 3, 2021 at 12:10

Roberto Hernandez · Accepted Answer · 2021-09-03 12:55:24Z

2

Try this

con = cx_Oracle.connect('***********')
cur = con.cursor()
faktura= 'QQ0009365/21'
numer_listu = '421435636356536'
kod_k = 112377
out_data = cur.var(str)
t = cur.callproc('QQ.ECOMMERCE.ZMIANA_OPISU_LIST_PRZEWOZOWY',[numer_listu,kod_k,faktura,out_data])
return out_data.getvalue()
except cx_Oracle.Error as error:
        print(error)

answered Sep 3, 2021 at 12:55

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

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

3 Comments

programmersd Over a year ago

ORA-06550: linia 1, kolumna 7: PLS-00221: 'ZMIANA_OPISU_LIST_PRZEWOZOWY' is not a procedure or is undefined ORA-06550: linia 1, kolumna 7: PL/SQL: Statement ignored

Roberto Hernandez Over a year ago

does your user has execute privilege over this package ? It looks like it doesn't

programmersd Over a year ago

I have all privilege for this procedure and database

Sharad · Accepted Answer · 2021-09-03 12:23:03Z

1

The out parameter must be declared as a cursor variable i.e.,

out_data = cursor.var(str)

The error indicates the wrong number of parameters are passed to the PL/SQL procedure.

Please go through the cx_Oracle documentation for calling pl/sql stored procedures through cx_Oracle: https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html#plsqlproc

answered Sep 3, 2021 at 12:23

Sharad

1711 gold badge1 silver badge11 bronze badges

Collectives™ on Stack Overflow

Problem with executing Oracle procedure by Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related