0

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?

1 Answer 1

1

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!

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.