Exception handling, PL-SQL Programming

Assignment Help:

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


Related Discussions:- Exception handling

Updating variables, Updating Variables For assignment, SQL uses the ke...

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Role of abstraction in pl/sql, Role of Abstraction in pl/sql: The abst...

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Triffic light control system, write the program for traffic control system ...

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

Predefined exceptions, Predefined Exceptions The internal exception is ...

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Introduction to oracle, Introduction Oracle 9i - it was made publ...

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

Mechanistis theory-haeckel - origin of life, MECHANISTI S THEORY-HAECKEL (...

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Execute immediate statement - syntax, EXECUTE IMMEDIATE Statement   Th...

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Appgen to sap data export, We are seeking a freelance consultant that is fa...

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Comparison operators- pl/sql, Comparison Operators The Comparison operat...

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd