Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Exception handling for after insert trigger to insert to remote table

Sekar_BLUE4EVERJun 28 2015 — edited Jun 29 2015

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 27 2015
Added on Jun 28 2015
2 comments
1,362 views