Exception Handling and Use of Cursors in PL/SQL Blocks

SQL is a non procedural language and so cannot do processing of either the data used to formulate the query or process the query. For example a SQL query cannot be placed in a loop or a dynamic SQL statement cannot be constructed by just using SQL alone. One requires a procedural language extension to perform some select data processing tasks in addition to DML capabilities offered bySQL statements. PL/SQL is the procedural language extension of SQL

Exception in SQL queries can occur when a Not Found condition or when a duplicate record found occurs. A Not Found condition occurs when a select query does not find any records as per the specified criterion. For example a select statement with a where clause need not always return records. This amounts to a NOTFOUND condition and is handled in a PL/SQL block. A PL/SQL block has a declare, begin, exception and end. So when a NOTFOUND condition occurs it has to be handled as an Exception in the PL/SQL block.Similarly when a SQL statement returns many rows it causes an exception referred to as DUPLICATE ROWS. Either way, these two exceptions have to be handled in the Exception handler and appropriate processing has to be done such as a raising a form trigger failure and posting an alert message.

In some situations an undefined exception can also occur with any errors other than NOTFOUND or DUPLICATE ROWS. In such cases exceptions can be raised with reference to a specific error number and remedial action.

SQL statements that return multiple rows can also be processed using a CURSOR. The cursor has to be declared, opened and executed record by record. Records can be processed individually based on a specific condition that is expected to occur. A cursor when opened has to be closed.

A cursor has to be fetched into local variables. The advantage of cursors is that one can formulate search expressions which would have been difficult using SQL statements alone.

A typical problem requiring the use of cursors is when one cannot state explicitly using an SQL statement which record in a table has to be updated. In this case the criterion for identifying the record that has to be updated can be stated clearly using procedural language constructs. For example in a table with columns Emp Id, DOB, DOJ if one requires to construct a query to identify employees who are less than 23 years of age when they joined in service.

Using cursors one can easily select the entire record to do the above without having to specify any cumbersome criterion in the where clause and just state the processing in the cursor body using PL/SQL constructs.


 By Srinivasa Gopal


Article Source:  Exception Handling and Use of Cursors in PL/SQL Blocks