-3

budling Oracle database function to move data between two schema that may has differences in structure as below and I trying to handling and log exception in log error table so function keep going when exception happen and just log it so I made below

returning the two values from failed insert and pass it to and insert in the exception part so but that function stop working and rollback and nothing in log recorded

what the issue

CREATE OR REPLACE FUNCTION FUN_INST
    RETURN NUMBER
IS
    V_STEP_NUM   NUMBER;
    vid          VARCHAR2 (32);
    vcrtUER      VARCHAR2 (32);
BEGIN
    SAVEPOINT INITI;

    V_STEP_NUM := 1;

    INSERT INTO TRACES (ID,
                        crtUER,
                        crtDAT,
                        upUSR,
                        upDAT)
        SELECT ID, crtUER, crtDAT, upUSR, upDAT FROM TRACES@dB_LINK1 RETURNING ID,CRTUER INTO VID,VCRTUER;

    V_STEP_NUM := 2;

    INSERT INTO Tusr (ID,
                      UERname,
                      crtDAT,
                      usrfname,
                      usrlname,
                      lastSeen,
                      lastAction)
    SELECT ID,
           UERname,
           crtDAT,
           usrfname,
           usrlname,
           NULL,
           NULL
      FROM Tusr@db_link1;

    RETURN (1);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX
    THEN
        INSERT INTO ERR_LOG (ERROR_MESSAGE,
                             PROGRAM_UNIT,
                             STEP_NUM,
                             INCIDENCE_DATE,
                             NOTE)
             VALUES ('Error',
                     'FUN_INST',
                     V_STEP_NUM,
                     SYSDATE,
                     'UNIQUE_CONSTRAINT_VIOLATED');
    WHEN OTHERS
    THEN
        ROLLBACK TO SAVEPOINT INITI;

        INSERT INTO ERR_LOG (ERROR_MESSAGE,
                             PROGRAM_UNIT,
                             STEP_NUM,
                             INCIDENCE_DATE,
                             NOTE)
             VALUES ('Error',
                     'FUN_INST',
                     V_STEP_NUM,
                     SYSDATE,
                     NULL);

        COMMIT;
        RETURN (2);
END FUN_INST;

1 Answer 1

1

The first INSERT statement will not work (does it even compile?).
It is a multi-row insert, so RETURNING INTO cannot be used in this way.

A better solution to achieve something like this, would be to use LOG ERRORS or BULK COLLECT with SAVE EXCEPTIONS.
You can find an example of both here:
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:1422998100346727312

Another thing: the DUP_VAL_ON_INDEX exception does not return a function result, nor does it (re)raise an exception.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.