Hi,
This error happens on a very simple INSERT which is a part of a large transaction. The strangest thing is that it doesn't always occur even though what is an almost identical transaction is executed (input to the application is identical). More than half of the attempts go through just fine. The only differences between transactions should be values fetched from sequences, and one case of the "now" timestamp being used (note: that happens far away from where the error seems to occur).
The application is Java and uses JDBC (version details below). The error happens while inserting very simple values into a very simple table, or at least that is where JDBC throws an SQLException. Thousands of similar INSERTs have occurred at that point already in that transaction.
The INSERT has no subqueries. It is simply the standard "INSERT INTO foo (...) VALUES (?,?,?...)". It has been checked, re-checked, re-re-checked etc that the JDBC PreparedStatement .set*() methods correspond to the types in the DB. PrepStmt.setObject() is used for some numeric columns, but in those cases we have checked that the parameter is either a Long or an Integer every single time.
So it seems weird that any string-to-number casts would be involved in any way.
The only trigger involved is included in the SQL snippet below, similar to what we use in lots of other tables without problems.
The transaction begins with SET CONSTRAINTS ALL DEFERRED, if that is in any way relevant here.
Any hints on what could be the problem are very welcome. I have read through http://www.orafaq.com/wiki/ORA-01722 but nothing seems to apply here.
Versions:
JDBC driver: thin 10.2.0.3.0
DB: 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Java: 1.5
The table:
CREATE TABLE foo(
id NUMBER(*,0) PRIMARY KEY,
foo_ident VARCHAR(32) NOT NULL,
foo_ns VARCHAR(4000) NOT NULL,
co1l VARCHAR(4000),
foo2_id NUMBER(*,0),
col3 VARCHAR(4000),
col4 VARCHAR(4000),
col5 NUMBER(1),
parent NUMBER(*,0),
col7 NUMBER(1),
col8 NUMBER(1),
foo3_id NUMBER(*,0),
col9 NUMBER(1),
col10 NUMBER(*,0),
foo4_id NUMBER(*,0),
col11 NUMBER(1),
col12 NUMBER(*,0),
FOREIGN KEY (foo4_id) REFERENCES foo4(id) ON DELETE CASCADE,
FOREIGN KEY (foo2_id) REFERENCES foo2(id) DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (parent) REFERENCES foo(id) ON DELETE CASCADE,
FOREIGN KEY (foo3_id) REFERENCES foo3(id) DEFERRABLE INITIALLY DEFERRED,
UNIQUE (foo_ident, foo_ns)
) ;
CREATE SEQUENCE foo_sequence
START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER foo_trigger
BEFORE INSERT
ON foo
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foo_sequence.nextval INTO :NEW.ID FROM dual;
END;