I have a PL/SQL function in my oracle database which returns a custom type which is a table of numbers. Running this function within a sql console works fine.
What's the correct way to get the result within a python script?
For build-in type I can do something like this:
return_value = cursor.var(cx_Oracle.STRING)
cursor.callfunc("myfunction", return_value, [list_of_params])
But I have no idea how to handle custom types.
If a function returns a custom type which is a nested table or varray, then it's ideal to read the values using TABLE function.
Assuming that your function definition is something like
create or replace function "myfunction"(p_in1 int, p_in2 varchar2)
return my_custom_type AS
..
..
You may do this and fetch results like any other query result. You don't have to pass the type name as well.
cursor.execute("""select * from TABLE( "myfunction"(:p_in1,:p_in2))""", (1,'TEXT') )
