NULL Values Are Not Reset Between Calls when Using the Same OracleCallableStatement (Doc ID 2701325.1)

Last updated on DECEMBER 15, 2021

Applies to:

Symptoms

With Statement reuse, the null parameter bind values set previously on a statement are not being set with new values; i.e.,  the null values are not (re)set between calls using the same OracleCallableStatement.

Detailed explanation:
There is a java application that uses the PlSqlArray methods of the OracleCallableStatement class (it is deprecated but not unsupported yet).  In the application, an OracleConnection and an OracleCallableStatement are opened once.  The statement is reused across calls.  An array of values is passed to the statement.  Whenever there is an element in the array that was populated in the first call, but null in the second call, then the second PL/SQL statement still has the non-null value of the first execution. 

Additional information:
- This issue occurs on a "SCAN" database as well as on a local XE instance.
- This issue is consistent.

Sample output:
Values in an array are generated for every third element and passed to PLSQL.

Set offset to 0 for initial run.
Java: Element content at 0 is num:0, code:Code0.
Java: Element content at 1 is num:1, code:null.
Java: Element content at 2 is num:2, code:null.
Java: Element content at 3 is num:3, code:Code3.
Java: Element content at 4 is num:4, code:null.
PLSQL: Element content at 1 is num:0, code:Code0.
PLSQL: Element content at 2 is num:1, code:.
PLSQL: Element content at 3 is num:2, code:.
PLSQL: Element content at 4 is num:3, code:Code3.
PLSQL: Element content at 5 is num:4, code:.

Change offset to 5, so different array elements will have null values.
Java: Element content at 0 is num:5, code:null.
Java: Element content at 1 is num:6, code:Code6.
Java: Element content at 2 is num:7, code:null.
Java: Element content at 3 is num:8, code:null.
Java: Element content at 4 is num:9, code:Code9.
PLSQL: Element content at 1 is num:5, code:Code0.
PLSQL: Element content at 2 is num:6, code:Code6.
PLSQL: Element content at 3 is num:7, code:.
PLSQL: Element content at 4 is num:8, code:Code3.
PLSQL: Element content at 5 is num:9, code:Code9.

Process finished with exit code 0

Description of sample output:
In call 1, for Java array codes, only array elements at index 0 and 3 have a value. This results in PLSql array elements at index 1 and 4 to have a value. This is correct.
In call 2, for Java array codes, only array elements at index 1 and 4 have a value. This results in PLSql array elements at index 1, 2, 4 and 5 to have a value. This is NOT correct.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.