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