Re: ora-03113 in INSTEAD OF trigger with database link

Martin wrote:

to add this:

OS: WindowsXP (used HP-UX as target also) Database: Oracle 9.2.0.5

>
> 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:57:34 CDT