Home » RDBMS Server » Networking and Gateways » EXCEPTION HANDLING for DBLINK In TRIGGERS (ORACLE 11gr2 linux)
EXCEPTION HANDLING for DBLINK In TRIGGERS [message #580719] Wed, 27 March 2013 05:20 Go to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Dear All,

I need some urgent support, am using easysoft odbc driver for connecting my oracle with sql server.

I have written below triger on my oracle db table, to insert the newly inserted data in sql as well. Now what i want is, if my dblink fails due to any reason, it should not effect my insertion on source oracle table on which i have created the triger.

create or replace
trigger trig_msg
after INSERT OR UPDATE OR DELETE ON msg
FOR each ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF     inserting THEN 
    INSERT INTO box@dnlink
        (
        "PNo","text"
        )
        VALUES
        (
        :NEW.PNo,:NEW.text
        );
  ELSIF  updating  THEN NULL;
  ELSIF  deleting  THEN NULL;
END IF;
commit;
END;


Please let me know how to achieve this.
Re: EXCEPTION HANDLING for DBLINK In TRIGGERS [message #580722 is a reply to message #580719] Wed, 27 March 2013 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is, most probably, not the best way to do that (but I don't know better). Therefore, until someone smarter than me offers a solution, here's one option. It is based on a presumption that you don't really care WHY insert over a database link failed, as WHEN OTHERS exception handler catches various errors. (Read about WHEN OTHERS and why you shouldn't use it).

OK, here we go. Create the same table in two schemas: SCOTT and MIKE:
SQL> show user
USER is "SCOTT"
SQL> create table abc (col number);

Table created.

SQL> connect mike/lion@ora10
Connected.
SQL> create table abc (col number);

Table created.

Back to SCOTT; create a database link and a trigger:
SQL> connect scott/tiger@ora10
Connected.
SQL> create database link dbl_mike
  2    connect to mike
  3    identified by lion
  4    using 'ora10';

Database link created.

SQL> create or replace trigger trg_ai_abc
  2    after insert on abc
  3    for each row
  4  begin
  5    insert into abc@dbl_mike (col) values (:new.col);
  6  exception
  7    when others then null;
  8  end;
  9  /

Trigger created.

Let's insert a record into SCOTT.ABC table; check whether it exists in MIKE.ABC:
SQL> insert into abc (col) values (1);

1 row created.

SQL> select * from abc;

       COL
----------
         1

SQL> select * from abc@dbl_mike;

       COL
----------
         1

So far, so good. Now, simulate an error (change MIKE's password, so database link is no longer valid):
SQL> connect mike/lion@ora10
Connected.
SQL> alter user mike identified by cat;

User altered.

Let's insert another record into SCOTT.ABC. WHEN OTHERS will "hide" that anything unusual happened:
SQL> connect scott/tiger@ora10
Connected.
SQL> insert into abc (col) values (2);

1 row created.

The outcome is the same as previously - 1 row created. But, what really happened?
SQL> select * from abc;

       COL
----------
         2
         1

SQL> select * from abc@dbl_mike;
select * from abc@dbl_mike
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBL_MIKE


SQL> connect mike/cat@ora10
Connected.
SQL> select * from abc;

       COL
----------
         1

SQL>

SCOTT.ABC contains 2 records (as you wanted), but MIKE.ABC contains only 1 record; although the INSERT operation over the database link failed, insert into owner's (SCOTT) table was successful.
Re: EXCEPTION HANDLING for DBLINK In TRIGGERS [message #580728 is a reply to message #580719] Wed, 27 March 2013 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The easiest way is to simply assume that the db link does work all the time and drop the trigger as anyway you accept to lose some cases this means you accept to lose all the cases.

Regards
Michel
Re: EXCEPTION HANDLING for DBLINK In TRIGGERS [message #585734 is a reply to message #580728] Wed, 29 May 2013 07:37 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Micheal,

Thanks for your response and raising this point to me, I would like to know, is there a way that i could store all exceptions in a table available on my source database ?

so that if there is any exception,i can achieve the below.
1) insert in original table to be successfull
2) exceptional record to be available for manual consideration in an exception table.

Thanks,
Re: EXCEPTION HANDLING for DBLINK In TRIGGERS [message #585738 is a reply to message #585734] Wed, 29 May 2013 08:01 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use what Littlefoot showed replacing "when others then null;" by an "when others then insert into <the exception table>;"

Regards
Michel
Previous Topic: ORA-12154: TNS:could not resolve service name
Next Topic: ORA-12154: TNS:could not resolve service name
Goto Forum:
  


Current Time: Thu Mar 28 03:54:59 CDT 2024