INVISIBLE column with custom %ROWTYPE type: unexpected end of data

[Jaza]

1. What versions are you using?

   Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
   Version 19.20.0.0.0
   
   platform.python_version: 3.11.0rc1
   
   oracledb.__version__: 2.1.1
   

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

   Error

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

   DPY-5006: unexpected end of data: want 1 bytes but only 0 bytes are available
   

4. Does your application call init_oracle_client()?

   No

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

   Setup SQL:

   CREATE TABLE foo_tmp (
       "id" NUMBER,
       "code" VARCHAR2(255 BYTE),
       "thingy" VARCHAR2(1 BYTE) INVISIBLE
   );
   
   INSERT INTO foo_tmp (id, code, thingy) values (1, 'foo', 'f');
   
   create or replace PACKAGE foo_test AS 
   
       TYPE foo_tmp_array IS TABLE OF foo_tmp%ROWTYPE
       INDEX BY BINARY_INTEGER;
   
       PROCEDURE prGetRecords (
           out_rec OUT foo_test.foo_tmp_array
       );
   
   END foo_test;
   
   create or replace PACKAGE BODY foo_test IS 
   
       PROCEDURE prGetRecords (
           out_rec OUT foo_test.foo_tmp_array
       ) 
       IS
           CURSOR c_foo_tmp IS
           SELECT *
           FROM foo_tmp;
       BEGIN
           OPEN  c_foo_tmp;
           FETCH c_foo_tmp BULK COLLECT INTO out_rec;
           CLOSE c_foo_tmp;
       END prGetRecords;
   
   END foo_test;
   

   Python script:

   import asyncio
   
   from oracledb import create_pool_async, makedsn
   
   
   async def list_data(pool):
       async with pool.acquire() as conn:
           data_coll_type = await conn.gettype("FOO_TEST.FOO_TMP_ARRAY")
           data_coll = data_coll_type.newobject()
   
           keyword_parameters = {
               "out_rec": data_coll,
           }
   
           proc_name = "foo_test.prGetRecords"
   
           await conn.callproc(
               name=proc_name,
               keyword_parameters=keyword_parameters,
           )
   
           rows = []
   
           for record in data_coll.aslist():
               row = {}
   
               for type_attr in data_coll.type.element_type.attributes:
                   attr_name = type_attr.name
                   attr_value = getattr(record, type_attr.name, None)
                   row[f"{attr_name}"] = attr_value
   
               rows.append(row)
   
           return rows
   
   
   async def main():
       dsn = makedsn(
           host="mydbhost",
           port=1521,
           sid="myservicename",
       )
   
       pool = create_pool_async(
           user="myuser,
           password="mypass",
           dsn=dsn,
       )
       rows = await list_data(pool)
       print(rows)
   
   
   asyncio.run(main())

   Expected output:

   [{'ID': 1.0, 'CODE': 'foo'}]
   

   Actual output:

   Traceback (most recent call last):
     File "/pathto/get_foo_records_test.py", line 54, in <module>
       asyncio.run(main())
     File "/usr/lib/python3.11/asyncio/runners.py", line 188, in run
       return runner.run(main)
              ^^^^^^^^^^^^^^^^
     File "/usr/lib/python3.11/asyncio/runners.py", line 120, in run
       return self._loop.run_until_complete(task)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
     File "/usr/lib/python3.11/asyncio/base_events.py", line 650, in run_until_complete
       return future.result()
              ^^^^^^^^^^^^^^^
     File "/pathto/get_foo_records_test.py", line 50, in main
       rows = await list_data(pool)
              ^^^^^^^^^^^^^^^^^^^^^
     File "/pathto/get_foo_records_test.py", line 30, in list_data
       attr_value = getattr(record, type_attr.name, None)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
     File "/pathto/lib/python3.11/site-packages/oracledb/dbobject.py", line 47, in __getattr__
       return self._impl.get_attr_value(attr_impl)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
     File "src/oracledb/impl/thin/dbobject.pyx", line 452, in oracledb.thin_impl.ThinDbObjectImpl.get_attr_value
     File "src/oracledb/impl/thin/dbobject.pyx", line 162, in oracledb.thin_impl.ThinDbObjectImpl._ensure_unpacked
     File "src/oracledb/impl/thin/dbobject.pyx", line 278, in oracledb.thin_impl.ThinDbObjectImpl._unpack_data
     File "src/oracledb/impl/thin/dbobject.pyx", line 316, in oracledb.thin_impl.ThinDbObjectImpl._unpack_data_from_buf
     File "src/oracledb/impl/thin/dbobject.pyx", line 346, in oracledb.thin_impl.ThinDbObjectImpl._unpack_value
     File "src/oracledb/impl/base/buffer.pyx", line 720, in oracledb.base_impl.Buffer.read_str
     File "src/oracledb/impl/base/buffer.pyx", line 634, in oracledb.base_impl.Buffer.read_raw_bytes_and_length
     File "src/oracledb/impl/base/buffer.pyx", line 730, in oracledb.base_impl.Buffer.read_ub1
     File "src/oracledb/impl/base/buffer.pyx", line 152, in oracledb.base_impl.Buffer._get_raw
     File "/pathto/lib/python3.11/site-packages/oracledb/errors.py", line 181, in _raise_err
       raise error.exc_type(error) from cause
   oracledb.exceptions.InternalError: DPY-5006: unexpected end of data: want 1 bytes but only 0 bytes are available
   

[Jaza]

Note: before switching to python-oracledb (and switching to async code), this worked fine for me using cx_Oracle 8.3.0:

from cx_Oracle import SessionPool, makedsn


def list_data(pool):
    with pool.acquire() as conn:
        data_coll_type = conn.gettype("FOO_TEST.FOO_TMP_ARRAY")
        data_coll = data_coll_type.newobject()

        keyword_parameters = {
            "out_rec": data_coll,
        }

        proc_name = "foo_test.prGetRecords"

        cursor = conn.cursor()
        cursor.callproc(
            name=proc_name,
            keyword_parameters=keyword_parameters,
        )

        rows = []

        for record in data_coll.aslist():
            row = {}

            for type_attr in data_coll.type.element_type.attributes:
                attr_name = type_attr.name
                attr_value = getattr(record, type_attr.name, None)
                row[f"{attr_name}"] = attr_value

            rows.append(row)

        return rows


def main():
    dsn = makedsn(
        host="mydbhost",
        port=1521,
        sid="myservicename",
    )

    pool = SessionPool(
        user="myuser",
        password="mypass",
        dsn=dsn,
    )
    rows = list_data(pool)
    print(rows)


main()

Actual output:

[{'ID': 1.0, 'CODE': 'foo'}]

[anthony-tuininga]

Thanks for the example. I'll take a look and get back to you!

[anthony-tuininga]

I made the changes necessary, I believe. If you are able to build from source you can verify that it works for you, too.

[Jaza]

@anthony-tuininga tested locally, works great for me. Thanks for fixing so quickly!

[anthony-tuininga]

This was included in version 2.2.0 which was just released.