Troubleshooting of errors in PL/SQL

Troubleshooting of errors in PL/SQL

In PL/SQL, error handling is an important part, just like the exception handling in the Write Program. The normal process implementation in the program may not be very complicated, but it takes more than half of the Code to handle all possible exceptions.

First, the exception handling in PL/SQL requires much attention, but the vast majority of people do not pay enough attention when writing PL/SQL, after data processing is complete, the Exception Processing ends in the following form:

When others then
Dbms_output.put_line ('error ......');

If you use when others as well, you should pay attention to it. Master tom also gave a detailed explanation in his book "deep database architecture". The following yellow sentence is quite touching.

• In my experience, error handling is done wrong more often than it is done right
• You don't have want to catch all exceptions
• You want to catch specific exceptions you are expecting (which means they aren't exceptions)
• You shoshould ignore all others-or of you do catch them to clean up a resource, RE-RAISE them

Oracle's PL/SQL team hoped that he would provide three suggestions in the improvement of the new version. His first suggestion was to remove when others, and the last part was retained, however, if the when others section in your PL/SQL does not contain any raise or raise_application_error, a warning will be given.

SQL> alter session set plsql_warnings = 'Enable: all ';
Session altered.

SQL> create or replace procedure test_proc
2
3 begin
4 dbms_output.put_line ('this is a test ');
5 exception
6 when others
7 then
8 dbms_output.put_line ('error handling ...');
9 end;
10/

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------------------------------------------------------------------------
1/1 PLW-05018: unit TEST_PROC omitted optional AUTHID clause;
Default value DEFINER used

6/6 PLW-06009: procedure "TEST_PROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR

The reason is that in PL/SQL Execution, for example, there are two Insert clauses.
Insert into t values (1 );
Insert into t values (2 );

This is the case in PL/SQL.
Savepoint insert_point1;
Insert into t values (1 );
If error then rollback to insert_point1;
Savepoint insert_point2;
Insert into t values (2 );
If error then rollback to insert_point2;
This step is equivalent to rollback in error handling if every atomic operation fails;
However, if you have already explicitly committed a transaction in data processing, it will be too late to roll back when you handle the error. The previous part has already been submitted.
This scenario is similar to the following scenario.
Insert into t values (1 );
Commit;
Insert into t values ('xxxxx'); -- this statement will report an error
Therefore, the first part of the entire transaction has been submitted, and the second part cannot be rolled back even if an error occurs.
Adding rollback to when others does not help.

You may simply print out the error information in more error handling processes. This processing result is equivalent to rewriting the error handling method,
The following implicit error handling will be overwritten, unless you have defined a reasonable error handling scenario and used raise or raise_application_error to correctly handle the error.
If error then rollback to insert_point1;
If error then rollback to insert_point2;

Therefore, we recommend that you do not use the when others clause in error handling. This general error processing may cause data inconsistency when there are more.
If you want to use when others then
You can use a format similar to the following.
Begin
Savepoint sp;
Process; -- this part is the specific processing code
Exception
When others then
Rollback to sp;
Dbms_output.put_line ('error, | sqlerrm );
End;
/
In this way, even if an exception occurs in a part of the code, the whole thing can be an atomic operation.

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills