Oracle Exception Handling

Oracle provides three types: pre-defined exceptions, non-pre-defined exceptions, and custom exceptions. Here, l pre-defined exceptions are used to handle common oracle errors; l non-pre-defined exceptions are used

Oracle provides three types: pre-defined exceptions, non-pre-defined exceptions, and custom exceptions. Here, l pre-defined exceptions are used to handle common oracle errors; l non-pre-defined exceptions are used

Oracle provides three types: pre-defined exceptions, non-pre-defined exceptions, and custom exceptions. Where

L pre-defined exceptions are used to handle common oracle errors;

L non-pre-defined exceptions are used to handle oracle errors that cannot be handled by pre-defined exceptions;

L custom exceptions handle other cases unrelated to oracle errors.

During Oracle code writing, if exceptions are caught, the running error will be resolved in the plsql block; otherwise, the error will be passed to the calling environment.

Common pre-defined exceptions:
To handle common oracle errors, plsql provides more than 20 predefined exceptions for developers. Each predefined exception corresponds to an oracle System error.

Access_info_null (ora-06530): triggered when an object without Initialization is accessed.

Case_not_found (ora-06592): this exception is triggered when there is no necessary conditional branch and no else clause after the case process.

Collection_is_null (ora-06531): access uninitialized set elements (nested tables or varray ).

Cursor_already_open (ora-06511): reopens the opened cursor.

Dup_val_on_index (ora-00001): When you type duplicate values in the column for the unique index in.

Invalid_cursor (ora-01001): attempts to extract content on an invalid cursor, for example, if the cursor is not opened.

Invalid_number (ora-01722): when attempting to convert an invalid string to a numeric type.

No_data_found (ora-01403): When you execute select into to return No rows, or reference an element not initialized in the index table.

Too_many_rows (ora-01422): When you execute select into to return more than one row of data.

Zero_pide (ora-01476): 0 as the divisor.

Subscript_beyond_count (ora-06533): when using a nested table or a varray set, if the referenced subscript exceeds last.

Subscript_outside_limit (ora-06532): when using a nested table or a varray set, if the referenced subscript is less than first.

Value_error (ora-06502): When performing a value assignment operation, if the variable length is insufficient to accommodate actual data.

Login_denied (ora-01017): an incorrect user name or password is provided when you connect to the database.

Not_logged_on (ora-01012): execution of plsql code triggers when the program is not connected to the oracle database.

Program_error (ora-06501): plsql internal issue.

Rowtype_mismatch (ora-06504): When a value assignment is performed, if the host cursor variable and PLSQL cursor variable return type are incompatible.

Self_is_null (ora-30625): If you call a member method on a null instance when using an object type.

Storage_error (ora-06500): exceeds memory space or the memory is corrupted.

Sys_invalid_rowid (ora-01410): invalid string attempt to convert to rowid type.

Timeout_on_resource (ora-00051): timeout error while waiting for the resource.

Handle non-predefined exceptions:
Oracle errors other than pre-defined in 21 described above are collectively referred to as non-pre-defined exceptions. The handling of such exceptions involves three steps: first, defining partial exceptions; then using progma exception (exception_name, exception_number) is used to establish an association between exceptions and oracle errors. In this case, the user is required to know the possible error numbers (except sqlcode, sqlerrm, and raise_application_error). The exception is finally captured and processed in the Exception Handling Section.

Declare

E_integrity exception

Pragma prediction_init (e_integrity,-2291 );

Begin

Sqlstatement;

Exception

When e_integrity then

Dbms_output.put_line ('data integrity error. ');

End;

Process custom exceptions:
Pre-defined exceptions and non-pre-defined exceptions are related to oracle errors, while custom exceptions are customized based on the specific circumstances of business processing. When using custom exceptions, you must first define the exceptions in the definition section declare, then trigger exceptions in the Execution Section (use raise statements), and finally capture and process exceptions in the Exception Handling Section.

Declare

Myexception exception;

Begin

If1 = 0 then

Raise myexception;

Endif;

Exception

When myexception then

Dbms_output.put_line ('asdf ');

End;

Use the exception function:
The oracle built-in functions sqlcode and sqlerrm are mainly used in the others processor to return the oracle error code and error messages respectively. In general, sqlcode returns the oracle error code identified by a negative number, unless the error is 'ora-01403: no data found ', the corresponding sqlcode is + 100. for user-defined exceptions, sqlcode returns + 1. If no exception is triggered, sqlcode returns 0.

Begin

Exception

When others then

Dbms_output.put_line (sqlcode | sqlerrm (sqlcode ));

End;

In Oracle, raise_application_error is used to customize error messages in plsql applications. Note that this process can only be used in database subprograms (processes, functions, packages, and triggers), rather than anonymous block and client subprograms. Syntax: raise_application_error (error_number, message [, [true | false]); error_number is used to define the error number, which must be a negative integer between-20000 and-20999; message is used to specify an error message, and the message length cannot exceed 2048 bytes. If the third parameter is true, the error will be placed in the previous error stack. If it is false (default) all previous errors are replaced.

Plsql compilation warning:
Plsql warnings can be divided into three types: severe is used to check possible unexpected or incorrect results, such as parameter alias problems; performance is used to check possible performance problems, for example, varchar2 type data is provided for the number column during the insert operation, informational is used to check the dead code in the subroutine, and all is used to check all warnings. To enable the database to issue a warning when compiling plsql subprograms, you need to set the initialization parameter plsql_warnings. This parameter can be set not only at the system level or session level, but also in the alter procedure command. Alter {system | session | procedure} set plsql_warnings = '{enable | disable: {all | performance | severe | informational}'; to check whether corresponding warning information exists, you must activate the warning check, re-compile the subroutine, and use the show errors command to display a warning error.

Createorreplaceprocedure my_test

Is

Begin

If1 = 0 then

Dbms_output.put_line ('test ');

Endif;

End;

SQL> alter procedure my_test compile plsql_warnings = 'Enable: all ';

Procedure altered

SQL> show errors;

Errors for procedure sys. MY_TEST:

LINE/COL ERROR

---------------------------------

10/5 PLW-06002: unexecutable code