Exception handling for after insert trigger to insert to remote table

Sekar_BLUE4EVER

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

Unknown

>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.

Unknown

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