Oracle Stored Procedure---exception

Oracle exceptions are predefined and custom exceptions (personally felt).

Predefined exceptions include anonymous exceptions and non-anonymous exceptions.

(Anonymous exception: No name, like ORA-00371;

Non-anonymous exception: name, like No_data_found.

Custom exceptions: Need to declare, business-related.

Exceptions are thrown with raise. After the exception capture, you can continue raise thrown up.

After the exception code block is captured, if you want to react to an exception to the application, use the

Raise_application_error (-20000, ' Custom exception: empty string. ', false);

or Raise_application_error (-20000, ' Custom exception: empty string. ');

The first argument is the error number, from-20000 to-20999.

The second parameter is an error message.

The third argument, the Boolean type, and the default false, can not write this argument.

True (added to the error stack), False (overwrite the error stack).

The effect that is performed in Plsql is the pop-up error balloon.

Create or Replace procedure Pro_ten (P_one in Varchar2, p_two out varchar2) is exception_customized_one exception; --Custom Exception 1 Exception_customized_two exception; --Custom exception 2 Exception_customized_three exception; --Custom Exception 3, binding pragma exception_init (exception_customized_three,-371) with Oracle internal anonymous exceptions;--for example: ORA-00371: Shared pool memory is out of the begin DBMS _output.put_line (' Chuan shen: ' | | ' P_one: ' | | P_one | |
                       ' P_one length: ' | | Length (P_one) | | ' Trim length after: ' | |
  Length (Trim (p_one)); if (P_one is null or trim (P_one) is null) then-note that this cannot be written as trim (p_one) = ', according to the experimental results raise
  E
  elsif (Trim (p_one) = ' 0 ' or trim (p_one) = ' 0.0 ') then raise;
  elsif (Trim (p_one) = ' 1 ') then raise Exception_customized_three; ELSE begin P_two: = P_one | |
      ' has changed '; Dbms_output.put_line (' return result: ' | | ' P_two: ' | |
    P_two);
  End

End If; Exception when Exception_customized_one then BEGIN Dbms_output.put_line (' exception-20000 occured, Custom exception: empty string.
      '); Raise_application_error (-20000, ' Custom exception: empty string.
    ', false);
  End When Exception_customized_two then Dbms_output.put_line (' exception-20001 occured, custom exception: number 0 appears.
      ');
      raise;--here, throw the exception up, and give it to the caller./* The custom exception bound with the inner exception is not captured because there is an internal exception Oracle detection and is automatically thrown.
      The purpose of our binding is to add to the situation that is not defined by others and let Oracle know what we define.
      This anomaly goes directly to the others code block * * */*when Exception_customized_three then dbms_output.put_line (SQLERRM); Raise exception_customized_three;*/when others then Dbms_output.put_line (' others exception.
    '); Dbms_output.put_line (SQLERRM)--error message raise;--Here the exception continues to throw up, to the caller processing end Pro_ten;

Test the above 3 exceptions:

Oracle predefined list of 21 non-anonymous exceptions:

access_into_null undefined object case_not_found case does not    contain the corresponding when, and is not set
Collection_ Is_null  Collection element uninitialized
curser_already_open   cursor has been opened
dup_val_on_index the   column corresponding to the unique index has a duplicate value

invalid_ Cursor   operations on an illegal cursor
invalid_number an   Embedded SQL statement cannot convert a character to a number
no_data_found    use SELECT INTO not return rows. or too_many_rows execution of SELECT into when the index table is not initialized    , the result set is more than one row
zero_divide      divisor 0

Subscript_beyond_ The count   element subscript exceeds the maximum value of a nested table or Varray subscript_outside_limit the subscript  as a negative number when using nested tables or Varray
value_error     when assigned, the variable length is not sufficient to accommodate the actual data
login_denied the     pl/sql application provides an incorrect user name or password when connecting to the Oracle database 
not_logged_on     Pl/sql Applications Access Data Program_error pl/sql internal issues without connecting to the Oralce database     , you may need to reload the data dictionary and 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 The     object method is invoked on a null object when the object type is used
storage_error    When running Pl/sql, the memory space
sys_invalid_id   Invalid rowid string

timeout_on_resource   Oracle is waiting for resources to exceed