Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 240 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Exception handling for after insert trigger to insert to remote table

Hi,
I want to create a after insert trigger on a local table to insert the data into a remote table.But there is a problem with exception handling when remote DB is down.
looked thru the oracle docs and I tried to use the insert in a procedure and used the procedure inside the trigger.
CREATE OR REPLACE PROCEDURE insert_row_proc AUTHID DEFINER AS no_remote_db EXCEPTION; -- declare exception PRAGMA EXCEPTION_INIT (no_remote_db, -20000); -- assign error code to exception BEGIN INSERT INTO tmp_3_dtbl VALUES ( :new.id,:new.id1,:new.id2 ); EXCEPTION WHEN OTHERS THEN INSERT INTO stbl_log VALUES ( :new.id,:new.id1,:new.id2, 'Could not insert row.'); RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.'); END; /
But I get the following errors as I have used new bind variab
les
Errors for PROCEDURE INSERT_ROW_PROC: LINE/COL ERROR -------- ----------------------------------------------------------------- 8/5 PLS-00049: bad bind variable 'NEW.ID' 8/13 PLS-00049: bad bind variable 'NEW.ID1' 8/22 PLS-00049: bad bind variable 'NEW.ID2' 13/16 PLS-00049: bad bind variable 'NEW.ID' 13/24 PLS-00049: bad bind variable 'NEW.ID1' 13/33 PLS-00049: bad bind variable 'NEW.ID2'
CREATE OR REPLACE TRIGGER checktr AFTER INSERT ON stbl FOR EACH ROW BEGIN insert_row_proc; END; /
Also got error in trigger creation as procedure is invalid.How do I solve this
Thanks
Answers
-
>But there is a problem with exception handling when remote DB is down.
When the remote DB is down, any SQL that tries to access the remote DB will FAIL.
You are trying to "push" data into a database that might be down or otherwise unavailable.
A solution is to use the remote DB to periodically "pull" the data into itself & avoid using a trigger that could fail.
-
1 Why are you inserting this in a different database? Your code can fail by definition, as the database might be unavailable
2 :NEW applies to triggers only, it is not available in procedures. Either put this in your trigger, or pass the original columns in parameters to your procedure
2 You are using WHEN OTHERS. Tom Kyte considers this to be a BUG, and he is right, as you are SUPPRESSING errors, the cause of the error will be unknown
Also: Why, after many years of posting here, you STILL don't specify your four digit Oracle version an platform?
--------------
Sybrand Bakker
Senior Oracle DBA