-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
1) What is Exception? 2) Types of exceptions? Pre Definied Exceptions: EXCEPTION_NAME EXCEPTION_NUMBER 1) NO_DATA_FOUND : This exception is raised when select does not return any row in PL/SQL block. ex: declare dbms_output.put_line('Welcome'); dbms_output.put_line('The sal is ....'||l_sal); end; Output: Note: In the above program, we get the output 'Welcome'. As we dont have any employee with empno 2255, select stmt does not return any row. Once an exception is raised, control will not execute the remaining stmts of executable section, searches for Exception section.
We can make sure that the program is completed normally by catching the exception using Exception section. Syntax: Declare Ex: declare dbms_output.put_line('Welcome'); dbms_output.put_line('The sal is ....'||l_sal); end; Output: Pl/SQL Procedure successfully completed.
2) TOO_MANY_ROWS: TOO_MANY_ROWS exception is raised, when select stmt returns more than one row. Ex: dbms_output.put_line('Welcome'); dbms_output.put_line('The sal is ....'||l_sal); end; Output: Note: As the select stmt returns more than one row, TOO_MANY_ROWS exception is raised. As we know, Once an exception is raised control will not execute the remaining lines of excutable section, searches for the Exception section. We can avoid abnormal termination of the program by catching the Exception. Ex: dbms_output.put_line('Welcome'); dbms_output.put_line('The sal is ....'||l_sal); end; Output: Welcome Pl/SQL Procedure successfully completed. 3) ZERO_DIVIDE: This exception is raised, when we divide a number by zero. Ex: Declare a := 10/0; dbms_output.put_line(a); Output: Note: As we are not catching the exception, program is terminated abnormally. As a developer, we need to make sure that programs are completed successfully at any case. Ex: Declare a := 10/0; dbms_output.put_line(a); end; Output: Pl/SQL Procedure successfully completed.
4) VALUE_ERROR: This exception is raised, when the value which is returned does not match with the datatype variable. Ex: Declare begin dbms_output.put_line('The employee name is...'||l_ename); Output: Note: We can avoid abnormal termination of the program by catching the exception using Exception Section. Ex: Declare begin dbms_output.put_line('The employee name is...'||l_ename); end; Output: 5) DUP_VAL_ON_INDEX: ex: create table student ( sno number(3) primary key, insert a row in the table: commit; begin Output: Note: We can avoid abnormai termination of the program by catching the exception. Ex: begin Output: When Others handler: Ex1: Declare a := 10/0; dbms_output.put_line(a); end; Output: Note:
Ex2: declare dbms_output.put_line('Welcome'); dbms_output.put_line('The sal is ....'||l_sal); end; Output: +++++++++++++++++++++++++++++++++++ Ex: First lets establish parent-child relationship between two tables. create table student2( sno number(3) primary key, insert into student2 values (101, 'arun',40); create table library2 ( roll_no number(3) references student2(sno), insert into library2 values (101,'Java'); commit; begin Output: Note: We are deleteting the row from the parent table and the corresponding row exists in the child table. So exception is raised. The exception which is raised in the above program is ORA-2292. This exception does not have any name. This is an example of non -predefinied exception. The following steps are to followed to handle non-pre definied exception. Step 1: Declare the exception Syntax: <Exception_name> Exception; Step 2: Associate the exception raise_application_error ( <exception_no> , <Exception_name> ); Step 3: Handle the exception Exception
Ex: In the follwoing program , we perform the three step process to handle Non-pre definied exceptions. Declare begin Exception Output:
3) User definied exceptions: These exceptions are definied by the user. Following steps are to be followed to handle user definied exceptions. Step 1: Declare the exception Ex: Declare begin if l_sal > 2000 then Exception Output: Using raise_application_error: raise_application_error is a procedure which is used to throw a user defined error error_number and error_message to the application. Ex: Declare select sal into l_sal from emp where empno = 7902; if l_sal > 2000 then dbms_output.put_line('The sal is ....'||l_sal); Ouptut: Error Reporting functions: |
Next |