Oracle stored procedure-exception

Oracle exceptions include pre-defined exceptions and custom exceptions (in my opinion ).

The predefined exceptions include anonymous and non-Anonymous exceptions.

(Anonymous exception: no name like ORA-00371;

Non-Anonymous exception: there is a name, such as no_data_found ).

Custom exception: It must be declared and related to the business.

Raise is used to throw exceptions. After an exception is captured, you can continue to throw raise.

After the Exception Code block is captured, if you want to report the exception to the application, use

Raise_application_error (-20000, 'custom exception: empty string! ', False );

Or raise_application_error (-20000, 'custom exception: Null String! ');

The first parameter is the error code, from-20000 to-20999.

The second parameter is the error message.

The third parameter, boolean type. The default value is false. You can leave this parameter unspecified.

True (added to the error stack) and false (overwrite the error stack ).

In PLSQL, an error prompt box is displayed.

Create or replace procedure pro_ten (p_one in varchar2, p_two out varchar2) is exception_mizmized_one exception; -- custom exception 1 unknown exception; -- custom exception 2 exception_customized_three exception; -- custom exception 3, bind Pragma exception_init (exception_customized_three,-371) with Oracle internal anonymous exception; -- Example: ORA-00371: insufficient Shared Pool memory begin dbms_output.put_line ('parameter: '|' P _ One: '| p_one | 'P _ one length:' | length (p_one) |' Length after trim: '| length (TRIM (p_one); If (p_one is null or trim (p_one) is null) then -- note that trim (p_one) cannot be written here) = ''. According to the experiment result, this is the raise prediction_customized_one; elsif (TRIM (p_one) = '0' or trim (p_one) = '0. 0 ') then raise prediction_customized_two; elsif (TRIM (p_one) = '1') then raise prediction_customized_three; else begin p_two: = p_one | 'has changed '; dbms_output.put_line ('Return result: '| 'P _ TWO:' | p_two); end; e Nd if; Exception when exception_customized_one then begin dbms_output.put_line ('exception-20000 occured, custom exception: Null String! '); Raise_application_error (-20000,' custom exception: empty string! ', False); end; When prediction_customized_two then dbms_output.put_line ('exception-20001 occured, custom exception: number 0! '); Raise; -- the exception is thrown up and handed to the caller for processing/* Custom exceptions bound to internal exceptions do not need to be captured, oracle is detected and thrown out automatically because of internal exceptions. The purpose of our binding is to add situations that are not defined by others so that Oracle can understand what we define. In this case, the exception is directly sent to the others code block * // * When exception_customized_three then dbms_output.put_line (sqlerrm); Raise exception_customized_three; */when others then exception ('oss! '); Dbms_output.put_line (sqlerrm); -- error message raise; -- the exception is thrown up and handed to the caller for processing end pro_ten;

Test the preceding three types of exceptions:

21 non-Anonymous exception lists predefined by Oracle: access_assist_null if the undefined object case_not_found case does not contain the corresponding when, the collection_is_null collection element is not set. The curser_already_open cursor has already opened dup_val_on_index. The unique value invalid_cursor on the column corresponding to the unique index. The invalid_number embedded SQL statement cannot convert characters. the number no_data_found. No rows are returned using select, or when too_many_rows that are not initialized in the Application Index Table executes select into, when the result set exceeds the zero_divide division of one row, the 0subscript_beyond_count element subscript exceeds the maximum value of the nested table or varray subscript_outside, when the subscript is specified as a negative value_error value, the variable length is insufficient to accommodate the actual data when the login_denied PL/SQL application connects to the Oracle database, an incorrect user name or password not_logged_on PL/SQL application is provided. If the application does not connect to the oralce database, internal problems of data program_error PL/SQL may occur. You may need to reinstall the data dictionary & pl. /The rowtype_mismatch primary cursor variable in the SQL System package is incompatible with the response type of PL/SQL cursor variables. When self_is_null is used as the object type, when the null object calls the object method storage_error to run PL/SQL, out of memory space sys_invalid_id invalid rowid string timeout_on_resource Oracle exceeds when waiting for resources