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
Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

Using Subqueries A subquery is a query (typically enclosed by parentheses) that appears within another SQL data manipulation statement. If evaluated, the subquery gives a va

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Updating a Variable Assignment of an attribute value in a variable of a structured type Synatx: SET SN.C = 'S2'; As in Example the entire statement is equivalent to a