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
Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo