Exception handling for Oracle stored procedures

1, in order to improve the robustness of the stored procedures, avoid running errors, when establishing a stored procedure should contain the exception handling part.

2, exception (EXCEPTION) is a PL/SQL identifier, including pre-defined exceptions, non-predefined exceptions, and custom exceptions;

3. Predefined exceptions refer to system exceptions provided by PL/SQL, and non-predefined exceptions are used to handle Oracle errors unrelated to predefined exceptions, such as integrity constraints, and custom exceptions are used to handle other exceptions to Oracle errors.

4. Raise_application_error is used for custom error messages, and the message number must be between -20000~-20999

Named system exception causes

Access_into_null undefined Object

If the corresponding when is not included in the Case_not_found case and is not set

Collection_is_null collection Element not initialized

Curser_already_open Cursor already open

Dup_val_on_index duplicate values on a column corresponding to a unique index

Invalid_cursor operation on an illegal cursor

Invalid_number Embedded SQL statements cannot convert characters to numbers

No_data_found using SELECT INTO not returning rows, or applying the index table uninitialized

Too_many_rows when performing a select INTO, the result set exceeds one row

Zero_divide Divisor is 0

Subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varray

Subscript_outside_limit the subscript is specified as a negative number when using a nested table or Varray

When Value_error is assigned, the variable length is not sufficient to accommodate the actual data

Login_denied PL/SQL application provides an incorrect user name or password when connecting to an Oracle database

not_logged_on PL/SQL applications access data asking data without connecting to the Oralce database

Program_error PL/SQL Internal issues, you may need to reload the data Dictionary & Pl./sql System Package

Rowtype_mismatch The primary cursor variable is incompatible with the return type of the PL/SQL cursor variable

Self_is_null calling an object method on a null object when using an object type

Storage_Error when running PL/SQL, out of memory space

SYS_INVALID_ID Invalid ROWID string

Timeout_on_resource Oracle timed out while waiting for resources

--Custom exception handling

CREATE OR REPLACE PROCEDURE Stu_proc

(

--Multiple separated by commas

v_id in number

) is

--Multiple separated by semicolons

V_MAX_ID number;

V_name VARCHAR2 (20);

V_raise EXCEPTION;

BEGIN

SELECT MAX (a.id) to v_max_id from student A;

IF v_id>v_max_id Then

RAISE v_raise;

END IF;

SELECT O.sname to v_name from student o WHERE o.id=v_id;

Dbms_output.put_line (' Student name: ' | | V_name);

EXCEPTION

When V_raise Then

Raise_application_error ( -20010, ' v_id not exists! ');

When No_data_found Then

Raise_application_error ( -20011, ' ERROR: not present! ‘);

END Stu_proc;

Exception handling for Oracle stored procedures