Multibyte string in query results in ORA-03120/ORA-03146 DatabaseError

[reuben-li]

Hi,

1. What versions are you using?

DB: Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
Client: oracledb.version: 1.2.2

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

DatabaseError

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

We are currently facing an issue where certain query strings that use to work with cx_Oracle are throwing either ORA-03120 or ORA-03146 errors. The same query using DBeaver client works so the issue is likely with oracledb.

Our code goes like:

conn = oracledb.connect(
user=user,
password=pw,
host=host,
port=port,
service_name=service_name
)

sql = """
    SELECT *
    FROM TABLE
    WHERE FIELD = 'カタカナ'
"""

with conn.cursor() as cur:
    cur.execute(sql)
    rows = cur.fetchall()

Which results in DatabaseError: ORA-03146: Invalid buffer length for TTC field

If we added a limit on the results, a different DatabaseError is thrown

sql = """
    SELECT *
    FROM TABLE
    WHERE FIELD = 'カタカナ'
    FETCH FIRST 10 ROWS ONLY
"""

This will result in ORA-03120: two-task conversion routine: integer overflow

After trial and error, we found that using bind variable helps to get us past this issue:

sql = """
    SELECT *
    FROM TABLE
    WHERE FIELD = :katakana
"""

with conn.cursor() as cur:
    cur.execute(sql, katakana='カタカナ')
    rows = cur.fetchall()

4. Does your application call init_oracle_client()?

No, we use the thin client. But the same queries work with cx_Oracle and DBeaver client.

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

See above.

Thanks!

[cjbj]

Thanks for the report.

To help up diagnose the issue, can you provide a SQL script that creates the table and inserts some problematic data?

What's the DB character set & national character set - see here for queries to run.

What OS are you running Python on?

What's your version of Python?

[reuben-li]

Hi, thanks for the prompt response.

As I don't have the necessary privileges to test table creation, I can't confirm if the following would reproduce the issue:

CREATE TABLE MSG (  
  MSG_TITLE NVARCHAR2(256) NULL,  
  CLASS NVARCHAR2(256) NULL  
  )

INSERT INTO MSG  
  (MSG_TITLE, CLASS)  
  VALUES('Title', 'カタカナ')

SELECT * FROM MSG WHERE CLASS = 'カタカナ'

DB charset: JA16SJISTILDE
DB national charset: AL16UTF16
OS: Linux and MacOS
Python version: 3.10.9

[anthony-tuininga]

That table and query work fine for me. I am, however, using database character set AL32UTF8 which is a universal character set. This may be relevant for your case. It would be useful to run this code:

import oracledb

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

value = 'カタカナ'
sql = f"select '{value}' from dual"

cursor.execute(sql)
print("result:", cursor.fetchall())

That gives me the following result:

result: [('カタカナ',)]

which shows that the encoding is working fine. Can you try the same with your database?

[anthony-tuininga]

One other thing: assuming that the above query works for you, can you also check the output of this query?

select dump(class) from msg

My results are as follows:

Typ=1 Len=8: 48,171,48,191,48,171,48,202

[reuben-li]

That table and query work fine for me. I am, however, using database character set AL32UTF8 which is a universal character set. This may be relevant for your case. It would be useful to run this code:

import oracledb

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

value = 'カタカナ'
sql = f"select '{value}' from dual"

cursor.execute(sql)
print("result:", cursor.fetchall())

That gives me the following result:

result: [('カタカナ',)]

which shows that the encoding is working fine. Can you try the same with your database?

Thanks for your suggestion.
This approach also results in the DatabaseError: ORA-03146: Invalid buffer length for TTC field error.

[reuben-li]

One other thing: assuming that the above query works for you, can you also check the output of this query?

select dump(class) from msg

My results are as follows:

Typ=1 Len=8: 48,171,48,191,48,171,48,202

Yes, I get a similar result (the string I gave was just a dummy one).

[cjbj]

When you say 'dummy one', do you mean this string doesn't reproduce the problem for you? We need to be using the same data as you.

Out of interest, can you confirm there is no problem when you enable Thick mode?

[reuben-li]

Sorry, I'm afraid I cannot share the specific strings we have in our DB as they are sensitive. However, this is a common issue with any Japanese multibyte string we have (as with the dummy).

We have not been able to try Thick mode as we are on M1 chips now, but it works on cx_Oracle on our old machines. Let me try to run this on rosetta on M1 or on a non-M1 machine.

[reuben-li]

OK I found one string you could try with:
ディテール
This one of the actual strings that causes the bug for us.

[anthony-tuininga]

I tried that string. It works just as well as the other one. I modified my code as follows:

import oracledb

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

value = 'ディテール'
sql = f"select '{value}' from dual"

cursor.execute(sql)
fetched_value, = cursor.fetchone()
print("Matches?", fetched_value == value)

The output shows that it matches. If you use this code, do you get the bug? Or are you using some other code?

[reuben-li]

I get a Matches? True with this code.

[anthony-tuininga]

That suggests that you may have corrupted data in your database. I modified my script as follows:

import oracledb

# oracledb.init_oracle_client()

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

value = 'ディテール'
sql = f"select '{value}', dump('{value}') from dual"

cursor.execute(sql)
fetched_value, dumped_value = cursor.fetchone()
print("Matches?", fetched_value == value)
print("Dumped value:", dumped_value)

Can you run the same and confirm that you get this value:

Dumped value: Typ=96 Len=15: 227,131,135,227,130,163,227,131,134,227,131,188,227,131,171

Assuming you do, run dump() on the column that isn't working properly for you and let me know what you get. Thanks!

[reuben-li]

Sorry, please ignore my previous post... I was using a connector to snowflake and not oracle!

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
Cell In [1], line 24
     21 value = 'ディテール'
     22 sql = f"select '{value}' from dual"
---> 24 cursor.execute(sql)
     25 fetched_value, = cursor.fetchone()
     26 print("Matches?", fetched_value == value)

File /opt/homebrew/lib/python3.10/site-packages/oracledb/cursor.py:378, in Cursor.execute(self, statement, parameters, **keyword_parameters)
    376 if parameters is not None:
    377     impl.bind_one(self, parameters)
--> 378 impl.execute(self)
    379 if impl.fetch_vars is not None:
    380     return self

File src/oracledb/impl/thin/cursor.pyx:133, in oracledb.thin_impl.ThinCursorImpl.execute()

File src/oracledb/impl/thin/protocol.pyx:383, in oracledb.thin_impl.Protocol._process_single_message()

File src/oracledb/impl/thin/protocol.pyx:384, in oracledb.thin_impl.Protocol._process_single_message()

File src/oracledb/impl/thin/protocol.pyx:377, in oracledb.thin_impl.Protocol._process_message()

DatabaseError: ORA-03120: two-task conversion routine: integer overflow

Above is the error dump from the first task you asked me to do when using oracledb

[anthony-tuininga]

I am able to replicate your issue when using your character set on the database. I'll get back to you on the solution once I have it!

[reuben-li]

Fantastic!

[anthony-tuininga]

I have pushed a patch that should correct this issue and added a relevant test case. If you are able to build from source you can verify that it corrects your issue as well.

[anthony-tuininga]

This has been included in version 1.3.0 which was just released.