| insert into another DB via trigger [message #12038] |
Tue, 27 April 2004 16:59  |
Jeffrey
Messages: 30
Registered: January 2003
|
Member |
|
|
Is it possible to write a trigger which will insert a record into a table which resides in another database instance? Provided a database link is created and active?
If it is possible, how would the insert statement look like?
|
|
|
|
|
|
|
|
|
|
| Re: insert into another DB via trigger [message #12163 is a reply to message #12073] |
Tue, 04 May 2004 19:25  |
Jeffrey
Messages: 30
Registered: January 2003
|
Member |
|
|
dear todd,
I have tried writing a sample to know how cathing/traping an exception works but somehow it would not compile.
CREATE OR REPLACE TRIGGER "SPID"."TESTING" AFTER
INSERT ON "SPID"."PA_SENARAI_LOT" FOR EACH ROW begin
if :new.Daerah='08' then
insert into Testing@pdk.US.ORACLE.COM values('JEFF','1');
exception
when others then
null;
end if;
|
|
|
|
|
|
| Re: insert into another DB via trigger [message #12229 is a reply to message #12163] |
Sun, 09 May 2004 19:50 |
Jeffrey
Messages: 30
Registered: January 2003
|
Member |
|
|
|
Ok, i have compiled the trigger and it works. Now i would like to make the exception a meaningful one. Meaning to say i would like to catch an exception just for a database link when it is down. How do i go about and write it?
|
|
|
|
| Re: insert into another DB via trigger [message #12230 is a reply to message #12229] |
Sun, 09 May 2004 22:07 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use pragma exception_init to associate whatever Oracle exceptions you want with your own erorr messages, then trap anything else in your when others, using sqlerrcode and sqlerrm, as in the example below, followed by some ora exceptions associated with links. In the example below, without the pragma exception_init, you would just get "database link not open", the message in the list below, associated with that error code.
CREATE OR REPLACE TRIGGER "SPID"."TESTING"
AFTER INSERT ON "SPID"."PA_SENARAI_LOT"
FOR EACH ROW
DECLARE
PRAGMA EXCEPTION_INIT (no_link, -2081); -- ORA-02081
BEGIN
IF :NEW.Daerah = '08' THEN
INSERT INTO Testing@pdk.US.ORACLE.COM VALUES ('JEFF', '1');
END IF;
EXCEPTION
WHEN no_link THEN
RAISE_APPLICATION_ERROR (-20002, 'Sorry, but the database link is not open'); -- your own customized message
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, sqlcode() || ': ' ||sqlerrm());
END;
/
ORA-01729 database link name expected
ORA-02011 duplicate database link name
ORA-02018 database link of same name has an open connection
ORA-02020 too many database links in use
ORA-02024 database link not found
ORA-02026 missing LINK keyword
ORA-02080 database link is in use
ORA-02081 database link is not open
ORA-02082 a loopback database link must have a connection qualifier
ORA-02085 database link string connects to string
ORA-02086 database (link) name is too long
ORA-02837 Unable to unlink temporary file
ORA-03112 a server linked as single-task cannot use SQL*Net
ORA-04051 user string cannot use database link string.string
ORA-04054 database link string does not exist
ORA-06808 TLI Driver: could not link IPX and ethernet streams
ORA-06815 TLI Driver: could not link SPX and IPX streams
ORA-09837 addCallback: could not add allocate a callback link.
ORA-09934 Link of current password file to old failed.
ORA-09935 Unlink of current password file failed.
ORA-12315 database link type is invalid for the ALTER DATABASE statement
ORA-12316 syntax error in database link's connect string
ORA-12317 logon to database (link name string) denied
ORA-12318 database (link name string) is already mounted
ORA-12319 database (link name string) is already open
ORA-12321 database (link name string) is not open and AUTO_MOUNTING=FALSE
ORA-12322 unable to mount database (link name string)
ORA-12323 unable to open database (link name string)
ORA-12324 cannot use the ROM: link type on a private database link
ORA-12333 database (link name string) is not mounted
ORA-12334 database (link name string) is still open
ORA-12335 database (link name string) is not open
ORA-12336 cannot login to database (link name string)
ORA-12345 user string lacks CREATE SESSION privilege in database link (linkname string)
ORA-12350 database link being dropped is still mounted
ORA-16580 bad Data Guard NetSlave network link
ORA-23396 database link "string" does not exist or has not been scheduled
ORA-23397 global name "string" does not match database link name "string"
ORA-23398 user name "string" at database link "string" does not match local user name "string"
ORA-24045 invalid agent address string, agent address should be of the form [[SCHEMA.]]NAME[[@DATABASE LINK]]
ORA-24084 DBLINK name in address field of agent string is not unique within the first 24 bytes"
ORA-24163 dblink is not supported in rules engine DDLs
ORA-24303 call not supported in non-deferred linkage
ORA-24777 use of non-migratable database link not allowed
ORA-25124 Database link name not allowed.
ORA-25426 remote instance does not support shared dblinks
ORA-28027 privileged database links may be used by global users
ORA-28501 communication error on heterogeneous database link
ORA-28502 internal communication error on heterogeneous database link
ORA-28504 ROWID not found in ROWID cache for heterogeneous database link
ORA-28510 heterogeneous database link initialization failed
ORA-28514 heterogeneous database link initialization could not convert system date
ORA-28557 unknown string for database link to non-Oracle system
ORA-32807 message system link string already exists
ORA-32808 message system link string does not exist
ORA-32815 message system link string is referenced by a foreign queue
ORA-32817 message system link string is not configured with a log queue for string
ORA-32820 subscriber queue and exception queue must use same message system link
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
