Exceptions are raised - Using Raise statement, PL-SQL Programming

How Exceptions Are Raised

By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number using an EXCEPTION_INIT. Though, other user-defined exceptions should be raised explicitly by the RAISE statements.

Using the RAISE Statement

The PL/SQL subprograms and blocks must raise an exception only when an error makes it undesirable or impossible to finish the processing. You can place the RAISE statements for a given exception anywhere within the scope of that exception. In the illustration below, you alert your PL/SQL block to a user-defined exception named out_of_stock:

DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER(4);

BEGIN

...

IF number_on_hand < 1 THEN

RAISE out_of_stock;

END IF;

EXCEPTION

WHEN out_of_stock THEN

-- handle the error

END;

You can also raise a predefined exception explicitly. In that way, an exception handler written for the predefined exception can process other errors, as the illustration below shows:

DECLARE

acct_type INTEGER;

BEGIN

...

IF acct_type NOT IN (1, 2, 3) THEN

RAISE INVALID_NUMBER; -- raise predefined exception

END IF;

EXCEPTION

WHEN INVALID_NUMBER THEN

ROLLBACK;

...

END;

Posted Date: 10/5/2012 5:16:38 AM | Location : United States







Related Discussions:- Exceptions are raised - Using Raise statement, Assignment Help, Ask Question on Exceptions are raised - Using Raise statement, Get Answer, Expert's Help, Exceptions are raised - Using Raise statement Discussions

Write discussion on Exceptions are raised - Using Raise statement
Your posts are moderated
Related Questions
TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security l

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

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

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.

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.


Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i