ORA-03113: EOF on communication channel occurs when TOracleDataSet inserts a LOB into a table, and this table has a trigger that tries to assign a value to another column in the new row:
:NEW.id := seq.NextValue
The error has been observed only after upgrading database to 9.2.0.2 with catpatch.
The error is reproducable in SQL PLus (see below).
The error does NOT occur if the RETURNING clause is omitted.
What can AllroundAutomations do about this?
Regards
Dag Pedersen
DIPS ASA
Script that reproduces the error:
---------------------------------
CREATE TABLE clobtest ( clb CLOB, nmbr NUMBER(10) );
CREATE OR REPLACE TRIGGER clobtest_b_i
BEFORE INSERT ON clobtest
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:NEW.nmbr := 1;
END;
/
VARIABLE clbvar CLOB;
BEGIN
INSERT INTO clobtest( clb )
VALUES( 'Hello CLOB!' )
RETURNING clb INTO :clbvar;
END;
/
Log from running the script
---------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
SQL> CREATE TABLE clobtest ( clb CLOB, nmbr NUMBER(10) );
Table created.
SQL> CREATE OR REPLACE TRIGGER clobtest_b_i
2 BEFORE INSERT ON clobtest
3 REFERENCING NEW AS NEW OLD AS OLD
4 FOR EACH ROW
5 BEGIN
6 :NEW.NMBR := 1; -- Assign value
7 END;
8 /
Trigger created.
SQL> VARIABLE clbvar CLOB;
SQL> BEGIN
2 INSERT INTO clobtest( clb ) VALUES( empty_clob() ) RETURNING clb INTO :clbvar;
3 end;
4 /
INSERT INTO clobtest( clb ) VALUES( empty_clob() ) RETURNING clb INTO :clbvar;
*
ERROR on line 2:
ORA-03113: End-of-file (EOF) on communication channel
------------------
:NEW.id := seq.NextValue
The error has been observed only after upgrading database to 9.2.0.2 with catpatch.
The error is reproducable in SQL PLus (see below).
The error does NOT occur if the RETURNING clause is omitted.
What can AllroundAutomations do about this?
Regards
Dag Pedersen
DIPS ASA
Script that reproduces the error:
---------------------------------
CREATE TABLE clobtest ( clb CLOB, nmbr NUMBER(10) );
CREATE OR REPLACE TRIGGER clobtest_b_i
BEFORE INSERT ON clobtest
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:NEW.nmbr := 1;
END;
/
VARIABLE clbvar CLOB;
BEGIN
INSERT INTO clobtest( clb )
VALUES( 'Hello CLOB!' )
RETURNING clb INTO :clbvar;
END;
/
Log from running the script
---------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
SQL> CREATE TABLE clobtest ( clb CLOB, nmbr NUMBER(10) );
Table created.
SQL> CREATE OR REPLACE TRIGGER clobtest_b_i
2 BEFORE INSERT ON clobtest
3 REFERENCING NEW AS NEW OLD AS OLD
4 FOR EACH ROW
5 BEGIN
6 :NEW.NMBR := 1; -- Assign value
7 END;
8 /
Trigger created.
SQL> VARIABLE clbvar CLOB;
SQL> BEGIN
2 INSERT INTO clobtest( clb ) VALUES( empty_clob() ) RETURNING clb INTO :clbvar;
3 end;
4 /
INSERT INTO clobtest( clb ) VALUES( empty_clob() ) RETURNING clb INTO :clbvar;
*
ERROR on line 2:
ORA-03113: End-of-file (EOF) on communication channel
------------------
