I have just posted a comment on this thread about what Tom Kyte consider as 99.99999999% a bug. I was wondering if, the following plsql code which I very often use when loading data bases, represents also a bug. A typical example is when we want to “initial load” a huge amount of client information. The requirements are:
- Do not stop the initial load in case of error occurring during the treatment of one record (client information)
- Log each record as “Successful” or “Not Successful with its corresponding Error”
- Make your process restartable
- Do not commit across fetch in order to avoid ORA-01555 rollback segment snapshot too old error
- In order to fulfil these requirements, I came always to such a kind of plsql code
BEGIN FOR r_client in c_client LOOP BEGIN -- set a savepoint here savepoint sps_a_savep; -- Create/Update/Delete or Do here what ever your want in order requirements -- to achieve you requirements -- Update successfull record UPDATEclient SET treated_record ='Y'; WHERE client_record = r_client.client_id; EXCEPTION WHEN OTHERS THEN -- Here there is a when other exception which is not followed by a RAISE ROLLBACKTO sps_a_savep; -- Update Unsuccessfull record with its error UPDATE client SET treated_record ='N'; error_code =SQLERRM WHERE client_record = r_client.client_id; END; -- Treat next record END LOOP; -- Commit outside the loop in order to avoid ORA-01555 COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; -- it is here were I want also to make a comment(see below) END;
Hopefully this case it one of 0.0000001 cases for which the WHEN OTHER EXCEPTION not followed by a RAISE is not a bug. But now I understand why Tom Kyte is always saying that it had better for the WHEN OTHER EXCEPTION that ORACLE has not created it. Because even if we use it and we use the RAISE command we will lose the exact plsql line number which really causes the exception to be raised.