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