SQL Record-plsql exception

PL/SQL exceptions

An error condition that occurs during program execution is known as a PL/SQL exception. PL/SQL enables programmers to use exception blocks in their programs to capture such conditions and take appropriate action to respond to error conditions. There are two types of exceptions:

• System-defined Exceptions

• User-defined exceptions

Exception handling syntax

The syntax for general exception handling is as follows. Here can be listed down a lot, to handle exceptions. The default exception will be handled using when others then:

DECLARE   <Declarations section>BEGIN <Executable command(s) >EXCEPTION <EXCEPTION handling goes here > when  exception1  Then exception1-handling-statements if exception2 then exception2-handling-statements when exception3 then exception3-handling-statements ... When others and then    exception3-handling-statementsEND;      

Example

Write some simple code to illustrate the concept. will use the Customers table that we have created and used in the previous chapters:

DECLAREC_ID Customers.Id%Type:= 8;C_name Customers.Name%Type;C_ADDR Customers.Address%Type;BEGIN SELECTName,AddressIntoC_name,C_addrFromCustomersWHEREId=c_id;Dbms_output.Put_Line(' Name: '||C_name);Dbms_outputput_line  ( Address: '  | |  C_addr); exception when No_data_found then Dbms_output. ( ' No such customer! '  when others then Dbms_ Output. Put_line ( ' error! ' end;               

When the above code is executed at the SQL prompt, it produces the following results:

No such customer!pl/sql procedure successfully completed.

The above program shows the name and address given by a customer's ID. Since there is no customer with an ID value of 8 in our database, the program runs with an exception no_data_found thrown, which is the catch exception exception block.

Throw exception

Exceptions are database servers that automate internal database errors, but exceptions can be explicitly presented by programmers using the command raise. The following is a simple syntax for throwing exceptions:

DECLARE   exception_name exception;  BEGINIFthen RAISE exception_name;  ENDIF;  When and then statement;  END;               

You can use the above syntax in a standard exception that throws Oracle or any user-defined exception. The next section shows an example of how to throw a user-defined exception that throws an Oracle standard exception and a similar method.

User-defined exceptions

PL/SQL allows you to define your own exceptions based on the needs of your program. User-defined exceptions must be declared and then explicitly presented using a raise statement or program Dbms_standard. Raise_application_error.

The syntax for declaring an exception is:

DECLARE   my-exception exception; 

Example:

The following example illustrates this concept. The program requires a customer ID, and when the user enters an invalid ID, the exception invalid_id thrown.

DECLAREC_ID Customers.Id%Type:= &cc_id;C_name Customers.Name%Type;C_ADDR Customers.Address%Type; --User Defined exceptionex_invalid_id EXCEPTION;BEGIN IFc_id<= 0 ThenRAISE ex_invalid_id; ELSE SELECTName,AddressIntoC_name,C_addrFromCustomersWHEREId=c_id;Dbms_output.Put_Line(' Name: '||C_name);Dbms_output.Put_Line(' Address: ' ||C_addr); END IF;EXCEPTIONWhenex_invalid_idthen Dbms_output. ( ' ID must be greater than zero! '  when No_data_found then  Dbms_output. ( ' No such customer! '  when others then Dbms_ Output. Put_line ( ' error! '  end;               

When the above code is executed at the SQL prompt, it produces the following results:

Enter value for cc_id:-6 (Let's enter a value-6) old  2:c_id customers.id%type: = &cc_id;new  2:c_id Customer S.id%type: = -6;id must be greater than zero!pl/sql procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are thrown by programs when any database rule is executed. For example, a pre-defined exception no_data_found when a SELECT INTO statement is raised to return a data row. The following table lists some of the important pre-defined exceptions:

Exception	Oracle Error	SQLCODE	Description
Access_into_null	06530	-6530	This exception is thrown when an empty object is automatically assigned a value
Case_not_found	06592	-6592	When there is no choice, it is thrown when a clause of a case statement is selected and there is no else clause
Collection_is_null	06531	-6531	Thrown when a program tries to request another collection method that does not have an uninitialized nested table or varray, or if the program tries to assign a value to an uninitialized nested table or an element of a variable-length array
Dup_val_on_index	00001	-1	Thrown when a duplicate value is attempted to be stored in a column with a unique index
Invalid_cursor	01001	-1001	Thrown when an attempt is made to make a cursor operation that is not allowed, such as closing an open cursor
Invalid_number	01722	-1722	When a character string is converted to a number that fails because the string does not represent a valid data being thrown
Login_denied	01017	-1017	When the program tries to log on to the database using an invalid user name or password is thrown
No_data_found	01403	+100	It is thrown when a SELECT INTO statement does not return any rows
not_logged_on	01012	-1012	It is thrown when a database call is made without connecting to the database
Program_error	06501	-6501	It is triggered when PL/SQL has an internal problem
Rowtype_mismatch	06504	-6504	A variable that has an incompatible data type is raised when the cursor is evaluated
Self_is_null	30625	-30625	It is thrown when the object's member method is called, but an instance of the object type is not initialized.
Storage_Error	06500	-6500	It is raised when PL/SQL memory is low or memory is corrupted
Too_many_rows	01422	-1422	It is thrown when the SELECT INTO statement returns multiple rows
Value_error	06502	-6502	Thrown when arithmetic, conversion, truncation, or size constraint errors
Zero_divide	01476	1476	It is thrown when a number tries to divide by 0.

SQL Record-plsql exception