"ORA-03106: fatal two-task communication protocol error" when CLOB is fetched as strings and then lob locators

[kubo]

ODPI-C version: 4.2.1 Client OS: Linux x86_64 (Ubuntu 20.04) Oracle Client: Instant Client 21.1 Server OS: Oracle Linux 7.9 on docker (The docker host is the above client os.) Oracle Server: 19.3 downloaded from OTN

9. Do you have a runnable code snippet to describe the problem?

I made the test code: https://gist.github.com/kubo/d893528c2294463f199eaf3f76be8097

Here is the output:

$ ./fetch-clob 
client version: 21.1.0.0.0
server version: 19.3.0.0.0
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
ERROR[53]: dpiStmt_fetch(stmt, &found, &index)
  message: ORA-03106: fatal two-task communication protocol error

The code does the following in short:

1. prepare a SQL statement including a CLOB column
2. execute the statement
3. define the CLOB column using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTE
4. fetch a row
5. release the statement
6. prepare exactly same SQL statement
7. execute the statement
8. define the CLOB column using DPI_ORACLE_TYPE_LONG_CLOB and DPI_NATIVE_TYPE_LOB
9. fetch a row --> "ORA-03106: fatal two-task communication protocol error"

The error occurs only when DPI_ORACLE_TYPE_LONG_VARCHAR is used before DPI_ORACLE_TYPE_LONG_CLOB. Any other combination looks fine.

$ ./fetch-clob cl  #  c: DPI_ORACLE_TYPE_LONG_CLOB, l: DPI_ORACLE_TYPE_LONG_VARCHAR
client version: 21.1.0.0.0
server version: 19.3.0.0.0
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
$ ./fetch-clob cccc  # DPI_ORACLE_TYPE_LONG_CLOB four times
client version: 21.1.0.0.0
server version: 19.3.0.0.0
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
$ ./fetch-clob llll  # DPI_ORACLE_TYPE_LONG_VARCHAR four times
client version: 21.1.0.0.0
server version: 19.3.0.0.0
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
$ ./fetch-clob ccllcc # DPI_ORACLE_TYPE_CLOB twice, DPI_ORACLE_TYPE_LONG_VARCHAR twice then DPI_ORACLE_TYPE_CLOB
client version: 21.1.0.0.0
server version: 19.3.0.0.0
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_LONG_VARCHAR and DPI_NATIVE_TYPE_BYTES
fetch clob data using DPI_ORACLE_TYPE_CLOB and DPI_NATIVE_TYPE_LOB
ERROR[53]: dpiStmt_fetch(stmt, &found, &index)
  message: ORA-03106: fatal two-task communication protocol error

If it isn't an issue in ODPI-C but an issue in Oracle server or Oracle client library (I guess so), feel free to close this.

[cjbj]

@kubo This looks like the case I mentioned in https://github.com/oracle/node-oracledb/issues/1388#issuecomment-860052165 If you have the environment still set up, can you set the statement cache size to 0 and see the behavior?

[kubo]

@cjbj Thanks. dpiConn_setStmtCacheSize(conn, 0) fixed the issue.

[cjbj]

It's an Oracle Client statement caching issue. We are hopeful some improvements will be forthcoming.