Scope rules - user-defined exceptions, PL-SQL Programming

Scope Rules

You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a block are considered local to that block and global to all its sub-blocks. As a block can reference only local or global exceptions, enclosing the blocks cannot reference the exceptions declared in the sub-block. If you re-declare a global exception in a sub-block, the local declarations prevail. Therefore, the sub-block cannot reference the global exception unless it was declared in the labeled block, in that situation the syntax below is valid:

block_label.exception_name

The illustration below describes the scope rules:

DECLARE

past_due EXCEPTION;

acct_num NUMBER;

BEGIN

DECLARE ---------- sub-block begins

past_due EXCEPTION; -- this declaration prevails

acct_num NUMBER;

BEGIN

...

IF ... THEN

RAISE past_due; -- this is not handled

END IF;

END; ------------- sub-block ends

EXCEPTION

WHEN past_due THEN -- does not handle RAISEd exception

...

END;

The enclosed blocks do not handle the raised exception as the declarations of past_due in the sub-block prevail. Though they share the similar name, the two past_due exceptions are unlike, just as the two acct_num variables share the similar name but are various variables. Hence, the RAISE statement and WHEN clause refer to various exception. To have the enclosing block handle the raised exception, you should remove its declaration from the sub-block or define the OTHERS handler.

Posted Date: 10/5/2012 5:12:42 AM | Location : United States







Related Discussions:- Scope rules - user-defined exceptions, Assignment Help, Ask Question on Scope rules - user-defined exceptions, Get Answer, Expert's Help, Scope rules - user-defined exceptions Discussions

Write discussion on Scope rules - user-defined exceptions
Your posts are moderated
Related Questions
Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Example of Foreign Key Constraint Example: Alternative formulation for 6.3 as a foreign key constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_take_exam

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene