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;