CODEWITHSUNDEEP
pythonoracle-database
finswimmer
finswimmer

Reputation: 15112

Getting return value of oracle function with python

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.

Upvotes: 0

Views: 1237

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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') )

Upvotes: 2

Related Questions