Is there a way to retry INSERT over DB link in Oracle, in case of connection error?

Question

DB procedure trying to insert in another Oracle database using DB link fails to perform the activity due to connection error. Is there a way to retry (say retry 3 times) INSERT over DB link in Oracle?

Error Text:

Error in INSERT in ITEM table....
ORA-03113: end-of-file on communication channel 
ORA-02063: preceding line from HRDBLINK

Please check my answer and accept it if it helped you or give a relevant feedback(comments) for the given solution. Please read : stackoverflow.com/help/someone-answers to understand why it's important. — Kaushik Nayak

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-06-07T04:10:09

Is there a way to retry (say retry 3 times) INSERT

This could be one way. Run the INSERT in a loop and skip the loop if at least one row is inserted, otherwise retry 3 times. One thing to remember though is that this will run insert 3 times if there were no rows inserted. You could mitigate it by checking before running your insert if that's the case.

SET SERVEROUTPUT ON;
DECLARE
dblink_err EXCEPTION;
PRAGMA EXCEPTION_INIT(dblink_err, -3113);

BEGIN
    FOR i IN 1..3 LOOP
        BEGIN
            INSERT INTO tst@urdblink ( id ) VALUES ( 3 );
            EXIT WHEN SQL%rowcount > 0;
        EXCEPTION
            WHEN dblink_err THEN
                dbms_output.put_line(sqlcode || ' ERROR occured: Retrying');
        END;
    END LOOP;
END;
/

Is there a way to retry INSERT over DB link in Oracle, in case of connection error?

1 Answers