Continued..
Adding a SUSPEND call resolves the issue. Records calculated before as well as
after the exception are now commited and the changed procedure below gets the
job done.
/M
SET TERM ^ ;
ALTER PROCEDURE P_LN_PC_PLAN_ALL_INSTRUMENTS2
RETURNS (
ID Varchar(20),
RESULT Varchar(100) )
AS
BEGIN
FOR SELECT ID
FROM LN_LOANS
WHERE COALESCE(DETAILS_LOCKED, 0)=0
INTO :ID
DO BEGIN
RESULT='SUCCESS';
EXECUTE PROCEDURE P_LN_PC_PLANINSTRUMENT(:ID);
SUSPEND;
WHEN ANY DO BEGIN
IN AUTONOMOUS TRANSACTION DO
INSERT INTO LN_PLAN_ERRORS (LOAN_ID, ERRORDATE, MSG)
VALUES (:ID, CURRENT_TIMESTAMP, 'GDSCODE: '||GDSCODE||', SQLCODE:
'||SQLCODE||', SQLSTATE: '||SQLSTATE);
RESULT='FAILED';
SUSPEND;
END
END
END ^
SET TERM ; ^
