These are my notes from the Oracle documentation on
Handling PL/SQL Errors.
Top
| Predefined (by the system) | User-defined |
| Named | Already named (ZERO_DIVIDE) or
Unnamed, but can be named | Must be named |
| Raised | Automatically by the system or with RAISE | With RAISE |
Top
Look for errors here:
/* Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you rollback the work that the main procedure was doing. -- (Investigate & study this) this */
Top
Handles errors from many statements with a single exception handler
Top
| Exception Name | SQLCode
(ORA- if
different) | Description |
|---|
| Access_Into_Null | -6530 | A program attempts to assign values to the attributes of an uninitialized object. |
| Case_Not_Found | -6592 | No Else and no When works |
| Collection_Is_Null | -6531 | Collection methods other than EXISTS are applied, or values assigned to the elements of an uninitialized nested table or varray. |
Cursor_Already
_Open | -6511 | An attempt to open an opened cursor or a cursor in a FOR loop. |
| Dup_Val_On_Index | 1 | Attempts to store duplicate values constrained by a unique index. |
| Invalid_Cursor | -1001 | i.e., closing an unopened cursor, etc. |
| Invalid_Number | -1722 | Raised from within SQL.
(In PL/Sql, Value_Error is raised.) or when a bulk FETCH's LIMIT-clause < 0 |
| Login_Denied | -1017 | Invalid username or password |
| No_Data_Found | +100
(-01403) | A SELECT INTO returns no rows, or a reference to a nested tables' deleted element or an uninitialized element in an index-by table. This exception is used internally by some SQL functions to signal that they are finished so don't rely on this exception being propagated if you raise it within a function that is called as part of a query. |
| Not_Logged_On | -1012 | A database call issued without being connected to Oracle. |
| Program_Error | -6501 | (contact Oracle support) PL/SQL has an internal problem. |
| Rowtype_Mismatch | -6504 | The host and PL/SQL cursor variables involved in an assignment have incompatible return types. i.e., when a variable is passed to a subprogram. |
| Self_Is_Null | -30625 | A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method. |
| Storage_Error | -6500 | (ran out of memory or memory corrupted) |
Subscript_
Beyond_Count | -6533 | A nested table is referenced or index number larger > collection. |
Subscript_
Outside_Limit | -6532 | A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. |
Sys_Invalid
_Rowid | -1410 | The character string does not represent a valid rowid. |
Timeout_On_
Resource | -51 | A time-out occurs while Oracle is waiting for a resource. |
| Too_Many_Rows | -1422 | A SELECT INTO statement returns more than one row. |
| Value_Error | -6502 | An arithmetic, conversion, truncation, or size-constraint error occurs.
(For SQL see Invalid_Number) |
| Zero_Divide | -1476 | A program attempts to divide a number by zero. |
Top
DECLARE
Error1 EXCEPTION;
Top
DECLARE
Error1 EXCEPTION;
BEGIN
---------- sub-block begins--------------------------
DECLARE
Error1 EXCEPTION; -- this overrides previous
BEGIN
IF 2 < 1 THEN RAISE Error1;
END IF;
END;
------------- sub-block ends ------------------------
EXCEPTION
/*----------------------------------------------*/
/* Enclosing blocks cannot reference exceptions */
/* declared in a sub-block */
/*----------------------------------------------*/
WHEN Error1
THEN DBMS_OUTPUT.PUT_LINE('Cannot handle');
WHEN OTHERS
THEN DBMS_OUTPUT.PUT_LINE('Error1 not seen');
END;
/
Top
To handle unnamed ORA- messages, use
PRAGMA EXCEPTION_INIT(defined_exception , -Oracle_error#);
DECLARE
Error1 EXCEPTION;
PRAGMA EXCEPTION_INIT(Error1, -60);
BEGIN
-- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN Error1 THEN
-- handle the error
END;
/
Top
DECLARE
num_tables NUMBER;
BEGIN
SELECT COUNT(*)
INTO num_tables
FROM USER_TABLES;
IF num_tables < 1000 THEN
Raise_Application_Error(-20101,'Need 1000 tables');
END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-20101: need 1000 tables
ORA-06512: at line 9
Top
-- Don't do it
Top
- Implicitly
- Internal (Ora-)
- User-defined exceptions set up through EXCEPTION_INIT
- Explicitly ('RAISE')
- If user-defined & not associated with an error #
- If a predefined should process other errors
Top
DECLARE
Error1 EXCEPTION;
BEGIN
IF 0 < 1 THEN RAISE Error1; /* user-defined */
END IF;
IF 7 > 9 THEN RAISE INVALID_NUMBER; /* predefined */
END IF;
EXCEPTION
WHEN Error1
THEN DBMS_OUTPUT.PUT_LINE('Error 1' );
WHEN INVALID_NUMBER
THEN DBMS_OUTPUT.PUT_LINE('Invalid #');
ROLLBACK;
END;
/
Top
If a handler can't be found in the current block, the exception propagates*, but the enclosing block may not be able to reference the name based on where it was declared.
*reproduces itself in successive enclosing blocks until a handler is found, and finally to the host environment if no handler found.
BEGIN
---------- sub-block begins-------------------------
DECLARE
sub_err EXCEPTION;
BEGIN
IF 1 < 2 THEN RAISE sub_err;
END IF;
END;
------------- sub-block ends------------------------
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END;
/
Top
* handle locally & then pass along
DECLARE
ErrNum EXCEPTION;
BEGIN
---------- sub-block begins -------------------------
BEGIN
IF 1 < 2 THEN RAISE ErrNum; END IF;
EXCEPTION
WHEN ErrNum THEN DBMS_Output.Put_Line('Some Stuff');
/*--------------------------*/
/* Raise to enclosing block */
/*--------------------------*/
RAISE;
END;
------------ sub-block ends -------------------------
EXCEPTION
WHEN ErrNum THEN DBMS_Output.Put_Line('More Stuff');
END;
/
Some Stuff
More Stuff
PL/SQL procedure successfully completed.
Top
EXCEPTION
WHEN exception1
OR exception2
OR VALUE_ERROR THEN
-- handle the error
Top
Exceptions raised in a declaration propagates immediately to the enclosing block.
Top
Exceptions raised in a handler propagates immediately to the enclosing block.
Top
You can use a GOTO only from a handler into an enclosing block.
Top
Create Table Errors ( Code Number
, Message Varchar2(64)
, Happened Timestamp
) ;
DECLARE
vSqlCode NUMBER;
vSqlErrM VARCHAR2(64);
BEGIN
some executable statements
EXCEPTION
WHEN OTHERS
THEN vSqlCode := SqlCode;
vSqlErrM := Substr(SqlErrM, 1 ,64);
Dbms_Output.Put_Line('Code '||vSqlCode
|| ': ' ||vSqlErrM);
--??---------------------------------------------------
--?? Normally we would call another procedure, declared
--?? with PRAGMA AUTONOMOUS_TRANSACTION, to insert
--?? information about errors. -- to be investigated
--??---------------------------------------------------
Insert Into Errors Values
( vSqlCode
, vSqlErrM
, SysTimestamp
);
End;
/
Top
Avoid by always coding OTHERS at the topmost level.
Top
To continue with the next statement, place a statement in a sub-block with an exception handler.
Top
The statement 'Print this 2nd' is never reached as an error is implicitly raised by dividing by zero.
Declare
Calc Number(8);
Begin
Dbms_Output.Put_Line('Print this 1st.');
Calc := 200/0;
Dbms_Output.Put_Line('Print this 2nd.');
Exception
When Zero_Divide
Then Dbms_Output.Put_Line('0 Divide Err');
End;
/
Print this 1st.
0 Divide Err
PL/SQL procedure successfully completed.
This is solved by putting an error-prone statement in a sub-block with an exception handler. If it fails, then it passes back out to the enclosing block, which continues with the next statement.
Declare
Calc Number(8);
Begin
Dbms_Output.Put_Line('Print this 1st.');
----- sub-block begins -----------------------------
Begin
Calc := 200/0;
Exception
When Zero_Divide
Then Dbms_Output.Put_Line
('Handle here then continue');
End;
----- sub-block ends ------------------------------
Dbms_Output.Put_Line('Print this 2nd.');
Exception
When Zero_Divide
Then Dbms_Output.Put_Line('0 Divide Err');
End;
/
Print this 1st.
Handle here then continue
Print this 2nd.
PL/SQL procedure successfully completed.
/* ?? You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as described in "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute". -- Investigate */
Top
- Encase the transaction in a sub-block.
- Place the sub-block inside a For/While loop to limit tries
- First, SAVEPOINT.
- If transaction...
- succeeds: commit and exit loop.
- fails: in handler rollback to savepoint, then fix the problem.
Drop Table Names Purge;
Create Table Names ( Name Varchar2(8) Primary Key );
Declare
vName Varchar2(20) := '&Name';
vCount Number;
Begin
For I In 1..5 Loop /*#2*/
-- sub-block begins------------------------/*#1*/
Begin
Savepoint StartHere; /*#3*/
/*-----------------------------------------*/
/* Raises Dup_Val_On_Index Since Same Name */
/*-----------------------------------------*/
Insert Into Names Values (vName);
Commit; /*#4.1*/
Exit; /*#4.1*/
Exception
When Dup_Val_On_Index /*#4.2*/
Then Rollback To StartHere;
vCount := vCount + 1;
vName := vName ||
To_Char(vCount);
End;
-- sub-block ends--------------------------------
End Loop;
End;
/
Top
If you need to know which statement failed:
DECLARE
stmt_no NUMBER;
name VARCHAR2(100);
BEGIN
stmt_no := 1;
-- some executable statement --
stmt_no := 2;
-- some executable statement --
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE(
'Error on '||stmt_no);
END;
/
Top
See the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS views.
Top
- SEVERE:
- something could cause unexpected behavior or wrong results
- PERFORMANCE:
- i.e., passing character to number column while inserting
- INFORMATIONAL:
- Things you might want to change (i.e., unreachable code)
- ALL
Top
This parameter can be set
Alter Session Set PLSql_Warnings='Disable:All';
Alter Session Set PLSql_Warnings='Enable:All';
Alter Session Set PLSql_Warnings='Enable:Performance';
Alter Session Set PLSql_Warnings='Enable:Severe'
, 'Disable:Performance'
, 'Error:06002'; -- PLW-06002 will halt compilation
Alter Procedure Loc_Var Compile
PLSql_Warnings='Enable:Performance'
Reuse Settings; -- Recompile With Extra Checking
Yes: PL/SQL subprograms
No: anonymous blocks
View with SHOW ERRORS or Select * from USER_ERRORS.
These have a PLW prefix.
Top
CREATE OR REPLACE PROCEDURE unreachable_code AS
x Number := 1;
BEGIN
IF x = 1 THEN DBMS_OUTPUT.PUT_LINE(1);
END IF;
END unreachable_code;
/
Procedure created.
SQL> /*---------------------------------------------------*/
SQL> /* enable all warning messages for this session */
SQL> /*---------------------------------------------------*/
SQL> CALL DBMS_WARNING.set_warning_setting_string(
'ENABLE:ALL' ,'SESSION');
Call completed.
SQL> /*---------------------------------------------------*/
SQL> /* Check the current warning setting */
SQL> /*---------------------------------------------------*/
SQL> SELECT DBMS_WARNING.get_warning_setting_string()
FROM DUAL;
DBMS_WARNING.GET_WARNING_SETTING_STRING()
---------------------------------------------------------------------------
ENABLE:ALL
SQL> /*---------------------------------------------------*/
SQL> /* Recompile the procedure and a warning about */
SQL> /* unreachable code displays */
SQL> /*---------------------------------------------------*/
SQL> ALTER PROCEDURE unreachable_code COMPILE;
SP2-0805: Procedure altered with compilation warnings
SQL> SHOW ERRORS;
Errors for PROCEDURE UNREACHABLE_CODE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8 PLW-06002: Unreachable code
SQL> Alter Procedure Unreachable_Code Compile
Plsql_Warnings = 'Enable:All' Reuse Settings;
Top
