How to pass table type in oracle using python(cx_oracle or oracledb)

Question

i want to pass table type in call_proc using python.

But i'm getting error while running.

PLS-00306: wrong number or types of arguments in call to 'REPORT_P'
ORA-06550: line 1, column 7:

PROCEDURE report_p
    (
        OUT_details     OUT SYS_REFCURSOR,
        IN_col      IN col,
        IN_com  IN  VARCHAR2,
        IN_id       IN NUMBER
    )

create or replace TYPE col AS OBJECT
(
    name        VARCHAR2(4000),
    mn        VARCHAR2(4000),
    fil    VARCHAR2(4000)

);

my code

out_val = cur.var(oracledb.CURSOR)
cur.callproc("report_p",(out_val,[],"Radio",21222))

This appears to be covered in the documentation: Changing Bind Data Types using an Input Type Handler — MT0
– MT0, Commented Jan 5, 2023 at 10:12

Anthony Tuininga · Accepted Answer · 2023-01-05 16:47:14Z

3

To pass an object like col you need to create one first. Something like this:

typ = conn.gettype("COL")
obj = typ.newobject()
obj.NAME = "NAME value"
obj.MN = "MN value"
obj.FIL = "FIL value"
ref_cursor = conn.cursor()
cursor.callproc("report_p", [ref_cursor, obj, "Radio", 21222])

answered Jan 5, 2023 at 16:47

Anthony Tuininga

7,2162 gold badges17 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

How to pass table type in oracle using python(cx_oracle or oracledb)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related