can't `gettype()` for a "table of records" type

[zea-vtl]

1. What versions are you using?

platform.platform: Linux-6.1.0-18-amd64-x86_64-with-glibc2.36
sys.maxsize > 2**32: True
platform.python_version: 3.11.4
oracledb.version: 2.4.1

2. Is it an error or a hang or a crash?

Error

3. What error(s) or behavior you are seeing?

> connection
<oracledb.Connection to MYUSER@MYSERVICE>

> connection.gettype("MY_PKG.TAB_OF_RECORDS_TYP")

TypeError                                 Traceback (most recent call last)
Cell In[5], line 1
----> 1 connection.gettype("MY_PKG.TAB_OF_RECORDS_TYP")

File ~/.cache/pypoetry/virtualenvs/vast-webapi-gexEMAwK-py3.11/lib/python3.11/site-packages/oracledb/connection.py:706, in Connection.gettype(self, name)
    701 """
    702 Return a type object given its name. This can then be used to create
    703 objects which can be bound to cursors created by this connection.
    704 """
    705 self._verify_connected()
--> 706 obj_type_impl = self._impl.get_type(self, name)
    707 return DbObjectType._from_impl(obj_type_impl)

File src/oracledb/impl/thin/connection.pyx:434, in oracledb.thin_impl.ThinConnImpl.get_type()

File src/oracledb/impl/thin/dbobject_cache.pyx:564, in oracledb.thin_impl.ThinDbObjectTypeCache.get_type()

File src/oracledb/impl/thin/dbobject_cache.pyx:445, in oracledb.thin_impl.BaseThinDbObjectTypeCache._populate_type_info()

File src/oracledb/impl/thin/dbobject_cache.pyx:277, in oracledb.thin_impl.BaseThinDbObjectTypeCache._parse_tds()

File src/oracledb/impl/thin/dbobject_cache.pyx:523, in oracledb.thin_impl.ThinDbObjectTypeCache._get_element_type_obj()

TypeError: 'NoneType' object is not iterable

4. Does your application call init_oracle_client()?

no

5. Include a runnable Python script that shows the problem.

create or replace package my_pkg as

   type typ_record is record(
      my_id              my_table.my_id%type,
      key_id             my_table.key_id%type,
      value              my_table.value%type,

   type tab_of_records_typ is table of typ_record;

end my_pkg;

my_engine = create_engine("<myconnectionstring>")
connection = my_engine.raw_connection()
try:
    driver_conn = connection.driver_connection
    connection.gettype("MY_PKG.TAB_OF_RECORDS_TYP")
finally:
    connection.close()

[cjbj]

Works for me. Possibly you have an older DB?

import os
import platform

import oracledb

assert 'ORACLE_USERNAME' in os.environ
assert 'ORACLE_PASSWORD' in os.environ
assert 'ORACLE_DSN' in os.environ

if os.environ.get('DRIVER_MODE') == 'thick':
    ld = None  # On Linux, pass None
    if platform.system() == 'Darwin':
        ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_23_3'
    elif platform.system() == 'Windows':
        ld = r'C:\oracle\instantclient_23_5'
    oracledb.init_oracle_client(lib_dir=ld)
    print('Using Thick mode')
else:
    print('Using Thin mode')

un = os.environ.get('ORACLE_USERNAME')
pw = os.environ.get('ORACLE_PASSWORD')
cs = os.environ.get('ORACLE_DSN')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:

        stmts = [
            """drop table if exists my_table""",
            """create table my_table (my_id number, key_id number, value varchar2(20))""",
            """create or replace package my_pkg as
               type typ_record is record(
                  my_id              my_table.my_id%type,
                  key_id             my_table.key_id%type,
                  value              my_table.value%type);
               type tab_of_records_typ is table of typ_record;
                end my_pkg;"""
        ]

        for s in stmts:
            cursor.execute(s)

    t = connection.gettype("MY_PKG.TAB_OF_RECORDS_TYP")
    print(t)

Works in both Thin & Thick modes:

Using Thin mode
<oracledb.DbObjectType CJ.MY_PKG.TAB_OF_RECORDS_TYP>

and

Using Thick mode
<oracledb.DbObjectType CJ.MY_PKG.TAB_OF_RECORDS_TYP>

[mac-vtl]

After digging further, the issue happens when the user is not the owner of the package. Below a modified version of the script provided that shows the error.

import os
import oracledb

assert 'OWNER_CONN_STRING' in os.environ
assert 'NON_OWNER_CONN_STRING' in os.environ

def parse_conn_string(conn_string):
    user_pass, connect_identifier = conn_string.split('@')
    username, password = user_pass.split('/')
    return username, password, connect_identifier

owner_un, pw, cs = parse_conn_string(os.environ.get('OWNER_CONN_STRING'))

with oracledb.connect(user=owner_un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:

        stmts = [
            """begin
                    execute immediate 'drop table my_table';
                exception when others then null;
                end;
            """,
            """create table my_table (my_id number, key_id number, value varchar2(20))""",
            """create or replace package my_pkg as
               type typ_record is record(
                  my_id              my_table.my_id%type,
                  key_id             my_table.key_id%type,
                  value              my_table.value%type);
               type tab_of_records_typ is table of typ_record;
               end my_pkg;
            """
        ]

        for s in stmts:
            cursor.execute(s)


for conn_string in [os.environ.get('OWNER_CONN_STRING'), os.environ.get('NON_OWNER_CONN_STRING')]:
    un, pw, cs = parse_conn_string(conn_string)

    print (f"Connecting to {cs} as {un}")
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
        udt_typ_record = connection.gettype(f"{owner_un.upper()}.MY_PKG.TYP_RECORD")
        print(udt_typ_record)
        udt_tab_record = connection.gettype(f"{owner_un.upper()}.MY_PKG.TAB_OF_RECORDS_TYP")
        print(udt_tab_record)

[cjbj]

Thanks - I can reproduce it. Looks like a check is missing and so the driver isn't giving a nice DPY-2035 or similar error message.

[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.

[mac-vtl]

thanks. works as expected. it now returns a clear message.

[anthony-tuininga]

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