callproc with a %ROWTYPE returns an error when Timestamp precedes a Varchar2

[RecycleBen]

It seems the callproc with a %ROWTYPE returns an error when Timestamp precedes a Varchar2 in the out variable. When you switch the order so that the timestamp is last there is no error. Or if you exclude the timestamp.

Template Info

1. What versions are you using? python-oracledb: 1.4.2 but the bug also occurs in 2.0.1

platform.platform: Windows-10-10.0.19045-SP0 sys.maxsize > 2**32: True platform.python_version: 3.11.6 oracledb.version: 2.0.1

3. Is it an error or a hang or a crash? no
4. What error(s) or behavior you are seeing?

The following conditions raises an error "UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa1 in position 9: invalid start byte"

• Using the thin clicent
• Using connection.gettype() using the %ROWTYPE syntax for a .callproc() with an out variable
• And the timestamp precedes a string column.

Note when running this with the thick client, the error is not raised. Maybe because the timestamp is included in the Attributes for the returned DbObjectType in the thick client but not the thin?

Stack Trace

(unicodetest) PS C:\Projects\training\python\oracledb\AttachSessionTest> python UnicodeTest4.3.py
<oracledb.DbObject UNICODETEST.SAMPLETEXT4%ROWTYPE at 0x20dbdac89d0>
Traceback (most recent call last):
  File "C:\Projects\training\python\oracledb\AttachSessionTest\UnicodeTest4.3.py", line 22, in <module>
    (textValue) = getattr(rowVar.getvalue(), "TextValue".upper())  # Error raised here
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Projects\training\python\oracledb\AttachSessionTest\unicodetest\Lib\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 440, 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 276, in oracledb.thin_impl.ThinDbObjectImpl._unpack_data
  File "src\\oracledb\\impl/thin/dbobject.pyx", line 314, in oracledb.thin_impl.ThinDbObjectImpl._unpack_data_from_buf
  File "src\\oracledb\\impl/thin/dbobject.pyx", line 341, in oracledb.thin_impl.ThinDbObjectImpl._unpack_value
  File "src\\oracledb\\impl/thin/buffer.pyx", line 779, in oracledb.thin_impl.Buffer.read_str
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa1 in position 9: invalid start byte

7. Does your application call init_oracle_client()? When the error is raised no it is using the thin client.

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

Create Schema as sysdba:

set define off

-- Create tablespace
create tablespace UnicodeTest_ts;

-- Create the user
create user UnicodeTest
  identified by <passwordhere>
  default tablespace UnicodeTest_ts
  temporary tablespace temp
  quota unlimited on UnicodeTest_ts
  profile default;

-- Grant/Revoke object privileges
grant execute on sys.dbms_crypto to UnicodeTest;
grant execute on sys.dbms_lock to UnicodeTest;

-- Grant/Revoke system privileges
grant administer database trigger to UnicodeTest;
grant create database link to UnicodeTest;
grant create procedure to UnicodeTest;
grant create sequence to UnicodeTest;
grant create session to UnicodeTest;
grant create table to UnicodeTest;
grant create trigger to UnicodeTest;
grant create view to UnicodeTest;
alter user UnicodeTest default role none;

(We don't need all of these grants but that is what I used.)

PLSQL Setup Code:

drop table UnicodeTest.SampleText4_t;
create table UnicodeTest.SampleText4_t
(
  Id                   NUMBER(14) not null,
  SampleTime           timestamp(6),
  TextValue            VARCHAR2(4)
)
;

Create or replace view UnicodeTest.SampleText4 as
select
  id,
  sampletime,
  textvalue
from unicodetest.sampletext4_t u;

insert into UnicodeTest.SampleText4_t values (99990000001149, systimestamp, 'High');
commit;

create or replace package UnicodeTest.pkg_Sample4 as

  procedure GetText (
    a_SampleWideRow                   out UnicodeTest.SampleText4%rowtype
  );

end pkg_Sample4;
/

create or replace package body UnicodeTest.pkg_Sample4 as

  procedure GetText (
    a_SampleWideRow                   out UnicodeTest.SampleText4%rowtype
  ) is
  begin

    select *
    into a_SampleWideRow
    from UnicodeTest.SampleText4
    where rownum = 1;

  end;

end pkg_Sample4;
/

(Note this is within the UncodeTest schema or use a different one. At first I had thought because we had Unicode characters we were getting this invalid start byte error because of the Unicode characters but it does error with just plain text.)

Python code:

import oracledb

# oracledb.init_oracle_client()  # Error with thin, no error in thick
connection = oracledb.connect("unicodetest/<passwordhere>@localhost:55164/posse")

cursor = connection.cursor()
cursor.arraysize = 2

sampleRowType = connection.gettype("UNICODETEST.SAMPLETEXT4%ROWTYPE")

rowVar = cursor.var(sampleRowType)

cursor.callproc("UnicodeTest.pkg_Sample4.GetText", (rowVar,))

textValueType = rowVar.getvalue()
print(textValueType)

# (textValue) = getattr(rowVar.getvalue(), "sampletime".upper()) # Doesn't exist in thin
(textValue) = getattr(rowVar.getvalue(), "TextValue".upper())  # Error raised here
print(textValue)

[anthony-tuininga]

I have been able to replicate the problem and am discussing internally on how to resolve the matter since it appears to be a bug in the database itself and not the driver. I'll let you know once I discover the source of the issue.

[RecycleBen]

Thanks Anthony!

[anthony-tuininga]

I have confirmed that it is a bug in the database but I have a workaround that I am implementing in the driver. I'll let you know once I have it ready.

[RecycleBen]

Ok. Great to hear there might be a workaround. Let us know. Thanks again!

[anthony-tuininga]

I have pushed a patch with the workaround that should work for you. If you are able to build from source you can verify that.

[RecycleBen]

Validated that it is working with the sample code above. Thanks!

[anthony-tuininga]

This patch was included in version 2.1.1 which was just released.