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

I need sql data base, I need SQL Data Base Project Description: Netwo...

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF...

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Pl/sql engine, Architecture The PL/SQL run-time system and compilation ...

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Is null operator-comparison operators, IS NULL Operator The IS NULL oper...

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Calculate the total shopper spending, Many of the reports generated from th...

Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Keyword and parameter description - cursors, Keyword and Parameter Descript...

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

%notfound - explicit cursor attributes, %NOTFOUND The %NOTFOUND is log...

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Using aggregation on nested tables - sql, Using Aggregation on Nested Table...

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Cursors in pl/sql, Cursors The Oracle uses work areas to execute the SQ...

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

How to use the explain plan for statement, Using the student and faculty ta...

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

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