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
Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Synonyms You can create the synonyms to provide location transparency for the remote schema objects like tables, views, sequences, stand-alone subprograms, and packages. Though,

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

IF Statement The IF statement executes a series of statement conditionally. Whether the series is executed or not depends on the value of the Boolean expression. Syntax:

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3