CODEWITHSUNDEEP
pythonoracle-databasefunctioncx-oracle
Teymur Eyvazov
Teymur Eyvazov

Reputation: 1

How call oracle function that returns record with cx_Oracle?

cx_Oracle has way to do it:

typeObj = connection.gettype("PKG_DEMO.UDT_DEMORECORD")
obj = typeObj.newobject()

but in documentation says:

 This feature is new in cx_Oracle 5.3 and is only available in Oracle
 Database 12.1 and higher.

In my case oracle server version is 11g and I can't change function itself. I think due to server version python raises error:

cx_Oracle.DatabaseError: ORA-04043: object PKG_DEMO.UDT_DEMORECORD does not exist

Is there a way to get record from oracle 11g?

Upvotes: 0

Views: 374

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

Unfortunately, no, at least not directly! You can use PL/SQL to break apart the record into bits and pieces, for example, if you have the PKG_DEMO.UDT_DEMORECORD record you can do something like the following:

import cx_Oracle
import datetime

conn = cx_Oracle.connect("pythondemo/welcome")
cursor = conn.cursor()

numVar = cursor.var(int)
strVar = cursor.var(str)
dateVar = cursor.var(datetime.datetime)
boolVar = cursor.var(bool)

numVar.setvalue(0, 6)
strVar.setvalue(0, "Test String")
dateVar.setvalue(0, datetime.datetime(2016, 5, 28))
boolVar.setvalue(0, False)

# show the original values
print("NUMBERVALUE ->", numVar.getvalue())
print("STRINGVALUE ->", strVar.getvalue())
print("DATEVALUE ->", dateVar.getvalue())
print("BOOLEANVALUE ->", boolVar.getvalue())
print()

cursor.execute("""
        declare
            t_Record pkg_Demo.udt_DemoRecord;
        begin
            t_Record.NumberValue := :numVar;
            t_Record.StringValue := :strVar;
            t_Record.DateValue := :dateVar;
            t_Record.BooleanValue := :boolVar;
            pkg_Demo.DemoRecordsInOut(t_Record);
            :numVar := t_Record.NumberValue;
            :strVar := t_Record.StringValue;
            :dateVar := t_Record.DateValue;
            :boolVar := t_Record.BooleanValue;
        end;""",
        numVar = numVar,
        strVar = strVar,
        dateVar = dateVar,
        boolVar = boolVar)

# show the modified values
print("NUMBERVALUE ->", numVar.getvalue())
print("STRINGVALUE ->", strVar.getvalue())
print("DATEVALUE ->", dateVar.getvalue())
print("BOOLEANVALUE ->", boolVar.getvalue())
print()

As you can it is much more complicated than the original demo -- but it does work!

Upvotes: 1

Related Questions