bind out variable causes ORA-03146 when using "insert .... returning ..."

[JuanPDP11]

1. What versions are you using?

I tried oracledb 1.2.0 and 1.1.1, both produce the same error.

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

Unhandled exception: Oracle ORA-03146.

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

Traceback (most recent call last):
  File "/<removed>/oracledb-repro.py", line 46, in <module>
    cursor.execute(
  File "/<removed>/.pyenv/versions/stage/lib/python3.9/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 133, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 384, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 377, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-03146: invalid buffer length for TTC field

4. Does your application call init_oracle_client()?
   No, The application is intended to use the thin client.
   If I include it, to use the THICK client, it WILL work. The issues is that bind out variables like the simple example in the documentation work in the thin client, but NOT the "Insert .... returning into :out_var" statement.
   There is NO documentation that would indicate that the THICK client is required.

5. Include a runnable Python script that shows the problem.
   This example is very similar to https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#bind-direction
   but it uses an "insert ... returning into :var" statement.

import sys
import platform
import oracledb

print("platform.platform:", platform.platform())
print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
print("platform.python_version:", platform.python_version())
print("oracledb.__version__:", oracledb.__version__)

connection = oracledb.connect(REMOVED)
cursor = connection.cursor()
sql_stmt = """
insert into Z_TABLE
    (
      COLUMN1,
      COLUMN2,
      COLUMN3
    ) values (
      Z_TABLE_SEQ.nextval,
      :in_bind_var1,
      :in_bind_var2
  )
returning COLUMN1 into :out_val
"""
out_val = cursor.var(int)
cursor.execute(
        sql_stmt,
        parameters=dict(
        in_bind_var1=144692, in_bind_var2=2, out_val=out_val
        ))
print(out_val.getvalue())

Include all SQL needed to create the database schema.

CREATE TABLE xxx."Z_TABLE"
   (	"COLUMN1" NUMBER,
	"COLUMN2" NUMBER,
	"COLUMN3" NUMBER(38,0),
	 CONSTRAINT "Z_TABLE_PK" PRIMARY KEY ("COLUMN1")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "DATA"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "DATA" ;

CREATE UNIQUE INDEX xxx."Z_TABLE_PK" ON xxx."Z_TABLE" ("COLUMN1")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "DATA" ;

CREATE SEQUENCE xxx.Z_TABLE_SEQ INCREMENT BY 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER ;

[anthony-tuininga]

Thanks for the report. I can reproduce it and will get back to you on the fix once I have it.

[anthony-tuininga]

The problem is because the regular expression used for finding RETURNING bind variables assumed the existence of a space character. I am adjusting this and adding a test case to ensure that the problem remains solved. Stay tuned!

[anthony-tuininga]

I've corrected the code and if you can build from source you can test directly yourself. The other option is to simply ensure a space character exists before the RETURNING keyword in your SQL. Thanks again for reporting this!

[JuanPDP11]

Hi Anthony,

Prefixing the RETURNING keyword with a space fixed the issue!
I'll try later to build it from the source but the workaround was simple and worked well.

Juan

[JuanPDP11]

I can confirm that, after building it from source, it worked well with the RETURNING keyword right after a new line without requiring the extra space.

Thank you very much for the very quick response!

Juan

[M4X1K02]

It may be useful to know that the same error occurs if no space nor line break was provided (which counts as a programming error is guess). I accidentally omitted the space and was searching for quite a while before seeing this thread. 🤦‍♂️

[anthony-tuininga]

Do you have an example that works in thick mode but fails in thin mode? If I need to further adjust the regex I can do so!

[M4X1K02]

I have also been working in thin mode and I just wanted to point out that the error message might have been a bit misleading given the fact that my query looked something like this:

"insert into Locations(" \
"latitude," \
"longitude) values (" \
":1," \
":2)" \
"returning locationId into :3"

Obviously, this multiline way of writing a single string makes it easier to see the columns with their respective variables, but also makes it hard to debug syntax errors like in the example above, where there should have been a space between the last two lines.
My spelling error lead to either the ORA-03146 error or an "Windows fatal exception: access violation" error being displayed. A missing space was the last error I could think of.
It's not a regex error or bug, only my own stupidity. Perhaps a scan for the "returning" keyword and preceding characters might be used for some kind of hint in the error message.

[anthony-tuininga]

Hmm, looks like thick mode (and therefore the server) allows for punctuation (like the closing parenthesis) before the returning statement. So I probably should adjust the regex to allow for that. That way it will work for you, too. :-)

[anthony-tuininga]

@M4X1K02, I've adjusted the regex to support your case, too. :-)

[M4X1K02]

I use conda for virtual environments and I don't know much about venv, so I can't confirm this atm, but I'll check after the next release.

[anthony-tuininga]

The fix for this bug has been included in python-oracledb 1.2.1 which was just released.