Oracle 9i 9.2.0.1 bug : insert error on tables with LONG datatype : Fixed in 9.2.0.3

Resolving The Problem

Inserts into tables with LONGs failed with 1 != 1 exceptions.

Oracle has confirmed the bug and the problem has been escalated to Oracle engineering. They now claim a fix in Oracle 9.2.0.3. Oracle also confirmed that the bug was introduced in Oracle9i because the Insert worked correctly in Oracle 8.1.7. Refer to TAR report below.

============

TAR Number 2812860.995 Open Date 07-FEB-03 16:26:03
Priority 2 Last Update 25-FEB-03 20:33:09
Product JDBC Product Version 9.2.0.2
Platform HP-UX PA-RISC (64-bit) Detailed Status Assigned to Development
TAR Reference n/a BUG Reference 2800548

Abstract

INSERT VIA JDBC RETURNS AN INVALID ROW COUNT OF 2 OR 3


Resolution History


07-FEB-03 16:26:04 GMT


Can you easily recover from, bypass or work around the problem? = NO
Does your system or application continue normally after the problem occurs? = NO
Are the standard features of the system or application still available; is the loss of service minor? = YES

### Detailed Problem Statement: ###
We are on Oracle 9.2.0.2. We have a Java application running on HP JDK 1.3.1_06 with Oracle Type IV 9.2.0.2.
We have a simple Java program that uses preparedStatements to insert records into a table with a LONG column. Sometimes, the executeUpdate returns a value of 2 or higher even though we insert a single record. The code snippet is as follows
String insertStmt = "insert into flow_table(sub_flow_key,config_xml) values
(?,?)";
PreparedStatement pstmt = null;
try {
pstmt = prepareCall(ctx,insertStmt);
pstmt.setString(1,subFlowKey);
pstmt.setChara cterStream(2,new
java.io.StringReader(aDBObject.configXML),aDBObject.configXM L.length());
int x = pstmt.executeUpdate();
if ( x!= 1 ){
System.out.println("********** x is: " + x);
throw getYFCException(new SQLException("Insert Record Count != 1 for
Statement ["+insertStmt+"]"),ctx.getDBType());
}
We think we know the problem.
Problem definition :
Problem 1: Non batched executeUpdate method of Oracle 9i thin driver CallableStatement returns incorrect count if the Statement is parameterized and reused.

Problem 2: AccumulateBatchResults has the reverse of the intended effect.
Description : If a parameterized CallableStatement is reused for multiple insert operations, the count of affected records returned from the executeUpdate method keeps incrementing with each invocation. Updates are not batched.
Steps To Reproduce:
1) Create the table as defined in the attached create_table.sql script.
2) Change the jdbc connection properties in the attached DBTest.java code to point to you database and schema and compile it.
3) Setup the Classpath to include the Oracle 9i driver and execute the class.

You will see that the count returned for each iteration keeps increasing.
If you use the Oracle 8i driver, the count returns the correct value (1) for each update. This is a big issue when working with application servers that cache these Statements.
Interestingly, I came across an article about setting the AccumulateBatchResult property to false. This property has no effect - this is expected because this scenario is not batched. But if you use a PreparedStatement instead (by calling prepareStatement instead of prepareCall on the connection), setting this Text continued in next action...

property has the reverse of the expected behavior.
It returns 1 with the property set to true (default value in 9i is true). But it returns an incrementing count if it is set to false. "False" is the suggested value in the Oracle documentation for the driver to be backward compatible with 8i. This property should either have no effect because there is no batching or if the driver automatically batches the statements (I don't know why), then it should behave as documented and not the other way around.

### What were you trying to accomplish when the problem occurred? ###
Just running our application which works in Oracle 8.1.7.

### The step-by-step actions which preceded the problem: ###
I will upload a test case that can recreate the problem.

### Has this ever worked? ###
Yes

### Can you reproduce the problem at will? ###
Yes

### Reproducible on other platforms, systems, environments? ###
Yes

### Known recent changes to your computer environment: ###
Upgraded to Oracle 9.2.0.2

07-FEB-03 16:27:04 GMT

Country: UNITED STATES
The customer has uploaded the following file via MetaLink:
C:\tmp\insert\create_table.sql


07-FEB-03 16:28:08 GMT

Country: UNITED STATES
The customer has uploaded the following file via MetaLink:
C:\tmp\insert\DBTest.java


07-FEB-03 17:22:47 GMT

wip

12-FEB-03 23:30:52 GMT

Testing the testcase with jdbc 9.2.0.1, I was able to see the behaviour that the customer is seeing.
if prepareStatement is used instead, then the application works fine similar to using jdbc 8.1.7.
PrepareCall in jdbc 8.1.7 does work as expected, but in 9.2.0.1, it only insert one record.
O logged a bug to verify if this is the intended behaviour or not?


17-FEB-03 16:22:23 GMT

The above bug was resolved in 9.2.0.3


18-FEB-03 19:18:49 GMT

Associated bug 2800548 has been updated and is still at status: 16.