Nested records - value not returned in specific cases · Issue #456 · oracle/python-oracledb (original) (raw)

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