Exception handling, PL-SQL Programming

Exception handling

In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The Examples of internally defined exceptions involve division by zero and out of memory. Some familiar internal exceptions have predefined names, like ZERO_DIVIDE and STORAGE_ERROR.

You can define exceptions of your own in the declarative part of any PL/SQL subprogram, block, or package. For illustration, you might define an exception namely the insufficient_funds to flag overdrawn bank accounts. Dissimilar internal exceptions, user-defined exceptions should be given names.

Whenever errors occur, an exception is raised. That is, the normal execution stops and control transfers to the exception-handling section of your PL/SQL subprogram or block. The Internal exceptions are raised implicitly (automatically) by the run-time system. The User-defined exceptions should be raised explicitly by the RAISE statements that can also raise the predefined exceptions.

To handle the raised exceptions, you write individual routines known as the exception handlers.

Later an exception handler runs, the present block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, the control returns to the host atmosphere.

In the illustration below, you compute and store a price-to-earnings ratio for a company with ticker symbol XYZ. The predefined exception ZERO_DIVIDE is raised whenever the company has zero earnings. This stops general execution of the block and transfers control to the exception handlers. The elective OTHERS handler catches all the exceptions which the block does not name explicitly.

DECLARE

pe_ratio NUMBER(3,1);

BEGIN

SELECT price / earnings INTO pe_ratio FROM stocks

WHERE symbol = 'XYZ'; -- might cause division-by-zero error

INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);

COMMIT;

EXCEPTION -- exception handlers begin

WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error

INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);

COMMIT;

...

WHEN OTHERS THEN -- handles all other errors

ROLLBACK;

END; -- exception handlers and block end here

The last illustration describes an exception handling, which is not the effective use of INSERT statements. For illustration, an enhanced way to do the insert is as shown:

INSERT INTO stats (symbol, ratio)

SELECT symbol, DECODE(earnings, 0, NULL, price / earnings)

FROM stocks WHERE symbol = 'XYZ';

Posted Date: 10/5/2012 5:04:12 AM | Location : United States







Related Discussions:- Exception handling, Assignment Help, Ask Question on Exception handling, Get Answer, Expert's Help, Exception handling Discussions

Write discussion on Exception handling
Your posts are moderated
Related Questions
How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty