Nested records types - Unable to assign values

[theObserver1]

What versions are you using?

database: 19c
platform.platform: Windows-10-10.0.19045-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.12.8
oracledb.version: 3.0.01

Is it an error or a hang or a crash?

Error

What error(s) or behavior you are seeing?

Similar to bug: #456

I need to work with a PLSQL record types defined inside a PLSQL package. The each record type will contain several other custom RECORD TYPES and TABLE collections defined as variables. The TABLE collections are working as expected so I omitted them from the code.

The issue is I cannot assign or access any values to DbObjectType record nested as an attribute inside a parent DbObjectType object created with GetType(). The script fails with a "OCI-21602: operation does not support the specified typecode" error. Please see Code Snippet.

Does your application call init_oracle_client()?

Yes.

Include a runnable Python script that shows the problem.

import oracledb

DB_DSN='dsn'
DB_USER='user'
DB_PASSWORD='password'

DDLS = [
"""
CREATE OR REPLACE PACKAGE test_pkg AS

TYPE NestedRecord_T IS RECORD (
  Amount                      VARCHAR2(8),
  Field2                      VARCHAR2(1)
);

TYPE main_t IS RECORD (
  RecordDate                  VARCHAR2(8),
  NestedRecord                NestedRecord_T
);

FUNCTION parse(TestData in out nocopy test_pkg.main_t) RETURN varchar2;

END test_pkg;
""",

"""
CREATE OR REPLACE PACKAGE BODY test_pkg AS

FUNCTION parse(TestData in out nocopy test_pkg.main_t) RETURN varchar2
IS
  result varchar2(8);
BEGIN
  return TestData.NestedRecord.Amount;

END parse;

END test_pkg;
"""
]

oracledb.init_oracle_client()
with oracledb.connect(user=DB_USER, password=DB_PASSWORD, dsn=DB_DSN) as db_conn:
    with db_conn.cursor() as cursor:
        for ddl in DDLS:
            cursor.execute(ddl)

        transaction_type_obj = db_conn.gettype('TEST_PKG.MAIN_T')
        trn_obj = transaction_type_obj.newobject()
        for each_field in trn_obj.type.attributes:

            if isinstance(each_field.type, oracledb.DbObjectType):
                print(f"Found nested DbObject type: {each_field.type.schema}.{each_field.type.name}")
                nested_obj = each_field.type.newobject()
                for attr in nested_obj.type.attributes:
                    print(f"Initializing attribute {attr.name} of type {attr.type.name}")
                    setattr(nested_obj, attr.name, "B")
                # Script will fail at this point with :- oracledb.exceptions.DatabaseError: OCI-21602: operation does not support the specified typecode
                setattr(trn_obj, each_field.name, nested_obj)

            else:
                print(f"Setting {each_field.name} to default value")
                setattr(trn_obj, each_field.name, "A")

        cursor_var =cursor.var(oracledb.STRING)
        result = cursor.callfunc("TEST_PKG.PARSE", oracledb.DB_TYPE_VARCHAR, [trn_obj])
        print(f"Func output is : '{result}' ")

        assert trn_obj.RECORDDATE == "A"
        assert trn_obj.NESTEDRECORD is not None
        assert trn_obj.NESTEDRECORD.FIELD2 == "B"

[anthony-tuininga]

This is not a bug but a known limitation. If you wish to have this behavior changed, you will have to log an enhancement request with Oracle Support.

Essentially, instead of trying to set the nested object you can simply get the nested object and manipulate it. Instead of this:

nested_obj = each_field.type.newobject()
for attr in nested_obj.type.attributes:
    print(f"Initializing attribute {attr.name} of type {attr.type.name}")
    setattr(nested_obj, attr.name, "B")
setattr(trn_obj, each_field.name, nested_obj)

do this:

nested_obj = getattr(trn_obj, each_field.name)
for attr in nested_obj.type.attributes:
    print(f"Initializing attribute {attr.name} of type {attr.type.name}")
    setattr(nested_obj, attr.name, "B")

The asserts you placed at the end of your script will pass as expected.

[theObserver1]

Thank you so much. I spent over a week trying to understand the problem !

But my large DBObject is crashing with memory issues when I try to set the value of the nest fields. Is there an alternative to using setattr without knowing the field name? The crash only states:- Process finished with exit code -1073741819 (0xC0000005)

[anthony-tuininga]

Hmm, that error implies a segfault of some sort. Are you able to replicate the issue consistently? And provide me a test case?

I'm not sure what you mean by "using setattr() without knowing the field name"? Can you clarify?

[theObserver1]

This test case triggers the segfault on my environment 100% of the time. The size of the VARCHAR2 variables doesn't seem to affect anything: it always triggers after FIELD8.

import oracledb

DB_DSN='dsn'
DB_USER='user'
DB_PASSWORD='password'

# ---------------------------------------------------------------------------------------------------------------------

DDLS = [
"""
CREATE OR REPLACE PACKAGE test_pkg AS

TYPE NestedRecord_T IS RECORD (
  Field1         VARCHAR2(2), 
  Field2         VARCHAR2(2), 
  Field3         VARCHAR2(2), 
  Field4         VARCHAR2(2), 
  Field5         VARCHAR2(2),
  Field6         VARCHAR2(2), 
  Field7         VARCHAR2(2), 
  Field8         VARCHAR2(2), 
  Field9         VARCHAR2(2), 
  Field10        VARCHAR2(2) 
 );

TYPE main_t IS RECORD (
  NestedRecord       NestedRecord_T
);

FUNCTION parse(TestData in out nocopy test_pkg.main_t) RETURN varchar2;

END test_pkg;
""",

    """
CREATE OR REPLACE PACKAGE BODY test_pkg AS

FUNCTION parse(TestData in out nocopy test_pkg.main_t) RETURN varchar2
IS
  result varchar2(8);
BEGIN
  return TestData.NestedRecord.Field2;

END parse;

END test_pkg;
"""
]

# --------------------------------------------------------------------------------------------------------------------

oracledb.init_oracle_client()
with oracledb.connect(user=DB_USER, password=DB_PASSWORD, dsn=dsn) as db_conn:
    with db_conn.cursor() as cursor:
        for ddl in DDLS:
            cursor.execute(ddl)

        transaction_type_obj = db_conn.gettype('TEST_PKG.MAIN_T')
        trn_obj = transaction_type_obj.newobject()
        for each_field in trn_obj.type.attributes:

            if isinstance(each_field.type, oracledb.DbObjectType):
                print(f"Found nested DbObject type: {each_field.type.schema}.{each_field.type.name}")
                nested_obj = getattr(trn_obj, each_field.name)
                for attr in nested_obj.type.attributes:
                    print(f"Initializing attribute {attr.name} of type {attr.type.name}")
                    setattr(nested_obj, attr.name, "B")

            else:
                print(f"Setting {each_field.name} to default value")
                setattr(trn_obj, each_field.name, "A")

        cursor_var = cursor.var(oracledb.STRING)
        result = cursor.callfunc("TEST_PKG.PARSE", oracledb.DB_TYPE_VARCHAR, [trn_obj])
        print(f"Func output is : '{result}' ")

        assert trn_obj.NESTEDRECORD is not None
        assert trn_obj.NESTEDRECORD.FIELD2 == "B"

Output

Found nested DbObject type:  TEST.NESTEDRECORD_T
Initializing attribute FIELD1 of type DB_TYPE_VARCHAR
Initializing attribute FIELD2 of type DB_TYPE_VARCHAR
Initializing attribute FIELD3 of type DB_TYPE_VARCHAR
Initializing attribute FIELD4 of type DB_TYPE_VARCHAR
Initializing attribute FIELD5 of type DB_TYPE_VARCHAR
Initializing attribute FIELD6 of type DB_TYPE_VARCHAR
Initializing attribute FIELD7 of type DB_TYPE_VARCHAR
Initializing attribute FIELD8 of type DB_TYPE_VARCHAR

Process finished with exit code -1073741819 (0xC0000005)

[anthony-tuininga]

I tried your test case on Linux and was unable to reproduce. I also tried it on Windows with 19.20 client and was unable to reproduce. Can you try with the latest instant client version for 19c? Can you also provide a call stack (using gdb or an equivalent tool on Windows)? My suspicions are that this is an Oracle Client library issue. If so, you will need to work with Oracle Support -- assuming that using the latest version of the client doesn't resolve it for you!

[theObserver1]

You are correct !

I download the Oracle Instant client version 23 _7, ran with :-
oracledb.init_oracle_client(lib_dir=r"instantclient_23_7")

And it worked.

Thank you so much. I spent two weeks tearing my hair out.