Nested records - value not returned in specific cases

[pvapenka-cen69045]

What versions are you using?

database: 21XE / 19c EE 19.0.0.0.0
platform.platform: macOS-15.3-arm64-arm-64bit-Mach-O
sys.maxsize > 2**32: True
platform.python_version: 3.13.0
oracledb.version: 2.5.1

Is it an error or a hang or a crash?

ERROR - incorrect behaviour of output variables (plsql records)

What error(s) or behavior you are seeing?

Depending on the definition of nested record (number of fields and their position) I get different outcomes.
Below is a runnable scripts that shows 2 failing cases which I believe should pass (for example filling a value in nested field INNER2 should return the record from the DB and contain the fillled value, however, None is returned l_i_am_causing_troubles_here field is the last field in the nested record).

To see the different behaviours, comment/uncomment the different definitions of inner_t record.

Does your application call init_oracle_client()?

NO - using thin mode

Include a runnable Python script that shows the problem.

import oracledb

DB_DSN='XEPDB1'
DB_USER='test'
DB_PASSWORD='test'

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

TYPE inner_t IS RECORD (
  ---------------------------------------
  -- works
  ---------------------------------------
  -- lnum                         NUMBER

  ---------------------------------------
  -- fails for test case: 1, 2
  ---------------------------------------
  -- l_i_am_causing_troubles_here NUMBER,
  -- lnum                         NUMBER

  ---------------------------------------
  -- fails for test case: NULL, 2
  ---------------------------------------
  lnum                         NUMBER,
  l_i_am_causing_troubles_here NUMBER
);

TYPE outer_t IS RECORD (
  inner1 inner_t,
  inner2 inner_t
);

FUNCTION parse(num1 NUMBER, num2 NUMBER) RETURN outer_t;

END test_pkg;
""",

"""
CREATE OR REPLACE PACKAGE BODY test_pkg AS

FUNCTION parse(num1 NUMBER, num2 NUMBER) RETURN outer_t
IS
  l_outer outer_t;
BEGIN
  l_outer.inner1.lnum := num1;
  l_outer.inner2.lnum := num2;
  RETURN l_outer;
END parse;

END test_pkg;
"""
]


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)

        outer_type = db_conn.gettype('TEST_PKG.OUTER_T')
        outer_var = cursor.var(outer_type)

        def parse(num1, num2):
            cursor.execute(
                """
                BEGIN
                  :result := test_pkg.parse(:num1, :num2);
                END;
                """,
                result=outer_var,
                num1=num1,
                num2=num2
            )
            return outer_var.getvalue()

        outer = parse(None, None)
        assert outer is not None
        assert outer.INNER1 is None
        assert outer.INNER2 is None

        outer = parse(1, 2)
        assert outer is not None
        assert outer.INNER1 is not None
        assert outer.INNER2 is not None
        assert outer.INNER1.LNUM == 1
        assert outer.INNER2.LNUM == 2

        outer = parse(1, None)
        assert outer is not None
        assert outer.INNER1 is not None
        assert outer.INNER2 is None
        assert outer.INNER1.LNUM == 1

        outer = parse(None, 2)
        assert outer is not None
        assert outer.INNER1 is None
        assert outer.INNER2 is not None
        assert outer.INNER2.LNUM == 2

        print("All tests passed")

[cjbj]

We'll take a look

[anthony-tuininga]

I can confirm that the issue exists and know its source. A patch will be forthcoming.

[anthony-tuininga]

I have pushed a patch that corrects this issue and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you can test your scenario and confirm the patch works as expected, that would be appreciated!

[pvapenka-cen69045]

Works well, thanks @anthony-tuininga

I just found it nice to have records with all null attributes to be None, but the way it works now makes sense, it mirrors the oracle behaviour and that is probably better :)

[anthony-tuininga]

This was included in python-oracledb 3.1.0 which was just released.