This document discusses implementing PL/SQL programs using exception handling. It defines exceptions as errors that disrupt normal program flow. There are two types of exceptions: user-defined and system-defined. System-defined exceptions are further divided into named and unnamed exceptions. The document provides an example program to demonstrate exception handling. It uses a SELECT statement with an invalid ID, which triggers the NO_DATA_FOUND exception that is caught and handled in the EXCEPTION block.
This document discusses implementing PL/SQL programs using exception handling. It defines exceptions as errors that disrupt normal program flow. There are two types of exceptions: user-defined and system-defined. System-defined exceptions are further divided into named and unnamed exceptions. The document provides an example program to demonstrate exception handling. It uses a SELECT statement with an invalid ID, which triggers the NO_DATA_FOUND exception that is caught and handled in the EXCEPTION block.
This document discusses implementing PL/SQL programs using exception handling. It defines exceptions as errors that disrupt normal program flow. There are two types of exceptions: user-defined and system-defined. System-defined exceptions are further divided into named and unnamed exceptions. The document provides an example program to demonstrate exception handling. It uses a SELECT statement with an invalid ID, which triggers the NO_DATA_FOUND exception that is caught and handled in the EXCEPTION block.
This document discusses implementing PL/SQL programs using exception handling. It defines exceptions as errors that disrupt normal program flow. There are two types of exceptions: user-defined and system-defined. System-defined exceptions are further divided into named and unnamed exceptions. The document provides an example program to demonstrate exception handling. It uses a SELECT statement with an invalid ID, which triggers the NO_DATA_FOUND exception that is caught and handled in the EXCEPTION block.
PRACTICAL: 16 Aim: Implement PL/SQL programs using exception handling.
Exception handling An exception is an error which disrupts the normal flow of program instructions. An error occurs during the program execution is called Exception in PL/SQL. PL/SQL provides us the exception block which raises the exception thus helping theprogrammer to find out the fault and resolve it.
There are two types of exceptions defined in PL/SQL
User defined exception.
System defined exceptions.
SYNTAX TO WRITE AN EXCEPTION
WHEN exception THENstatement;
DECLARE declarations section; BEGIN executable command(s); EXCEPTION WHEN exception1 THENstatement1; WHEN exception2 THENstatement2; [WHEN others THEN] /* default exception handling code */ END;
SYSTEM DEFINED EXCEPTIONS:
These exceptions are predefined in PL/SQL which get raised WHEN certain database rule is violated.
System-defined exceptions are further divided into two categories:
Named system exceptions.
Unnamed system exceptions.
Named system exceptions: They have a predefined name by the system like ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED etc. the list is quite big.
So we will discuss some of the most commonly used exceptions:
Example of exception handling Let's take a simple example to demonstrate the concept of exception handling. Here we are using the already created CUSTOMERS table.
A. Y. DADABHAI TECHNICAL INSTITUTE KOSAMBA, SURAT Page 1
DECLARE c_id customer22090.id%type := 8; c_name customer22090.name%type; c_addr customer22090.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customer22090 WHERE id = c_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such customer!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
OUTPUT After the execution of above code at SQL Prompt, it produces the following result:
The above program should show the name and address of a customer as result whose ID is given. But there is no customer with ID value 8 in our database, so the program raises the run-time exception NO_DATA_FOUND, which is captured in EXCEPTION block.
If you use the id defined in the above table (i.e. 7090 to 7079), you will get a certain result. For a demo example: here, we are using the id 7090.
A. Y. DADABHAI TECHNICAL INSTITUTE KOSAMBA, SURAT Page 2
RDBMSPRACTICAL - 16
DECLARE c_id customer22090.id%type := 7090; c_name customer22090.name%type; c_addr customer22090.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customer22090 WHERE id = c_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such customer!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
OUTPUT: After the execution of above code at SQL prompt, you will get the following result:
CONCLUSION:
Hence we perform PL/SQL programs using exception handling.
Progressive Assessment Sheet as per Rubrics
Regular Level of Attendance Presentation Total Assessment Understanding 05 05 07 08 25
Sign : Date:
A. Y. DADABHAI TECHNICAL INSTITUTE KOSAMBA, SURAT Page 3
