| EXCEPTION HANDLING for DBLINK In TRIGGERS [message #580719] |
Wed, 27 March 2013 05:20  |
 |
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  |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
