bind out variable causes ORA-03146 when using "insert .... returning ..." · Issue #104 · oracle/python-oracledb (original) (raw)

1. What versions are you using?

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

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

Unhandled exception: Oracle ORA-03146.

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

1. 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.
2. 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 ;