ora-03113 in INSTEAD OF trigger with database link

From: Martin <martin.lutz_at_unionall.de>
Date: Tue, 21 Jun 2005 14:49:40 +0200
Message-ID: <d992d6$p6t$1@online.de>

Hello,

we have a strange behaviour here in a combination of a VIEW, INSTAED OF TRIGGER and DATABASE LINK. While the INSERT works, any UPDATE or DELETE statement will fail with

ORA-03113: end-of-file on communication channel

The task is to allow the administration of components in database "A" from a gui-application running on database "B". To accomplish this we are creating a view pointing to the remote table via a database link. So we can modify the config-entries in the remote table through the view. For some colums we need a processing before we send them to the remote database, so the instead-of trigger is supposed to do that work.

Below there is an easy example for that behaviour. Any hints for a solution or how to track the problem further down?

Smile

Martin

Example:

CREATE TABLE test

     (tabcol                          VARCHAR2(200))

whether this db-link points to the same db-instance or to a "real"
remote database seems to make no difference
user for dblink was the owner of the table 'test'
--
make this fit to your environment
CREATE DATABASE LINK dblink CONNECT TO dblink_user IDENTIFIED BY dblink_pw USING 'dblink' /

connect as a different user
CREATE OR REPLACE VIEW vw_test ( tabcol ) AS select tabcol from test_at_dblink /

CREATE OR REPLACE TRIGGER iir_vw_test
  INSTEAD OF
   INSERT
  ON vw_test
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
   INSERT INTO test_at_dblink (

     tabcol
     ) VALUES (
     :NEW.tabcol
     );

END;
/

CREATE OR REPLACE TRIGGER idr_vw_test
  INSTEAD OF
   DELETE
  ON vw_test
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
   DELETE FROM test_at_dblink
   WHERE tabcol = :OLD.tabcol
   ;
END;
/

CREATE OR REPLACE TRIGGER iur_vw_test
  INSTEAD OF
   UPDATE
  ON vw_test
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
   UPDATE test_at_dblink
   SET tabcol = :NEW.tabcol
   WHERE tabcol = :OLD.tabcol
   ;
END;
/

SQL*Plus as owner of the created view

SQL> insert into vw_test values ('aaa');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vw_test;

TABCOL

aaa

SQL> update vw_test set tabcol = 'bbb' where tabcol = 'aaa'; update vw_test set tabcol = 'bbb' where tabcol = 'aaa' *
ERROR at line 1:
ORA-03113: Unerwartetes Übertragungsende in Kommunikation

this error is "unexpected end-of-file"
DELETE will fail with the same error

Received on Tue Jun 21 2005 - 07:49:40 CDT