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.


pe_ratio NUMBER(3,1);


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);


EXCEPTION -- exception handlers begin

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

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



WHEN OTHERS THEN -- handles all other errors


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
Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Identifiers You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages.

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th