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

Assignment Help:

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.


Related Discussions:- Scope rules - user-defined exceptions

Data types in sql - integer, Data Types in SQL - Integer INTEGER or  s...

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

How to use the explain plan for statement, Using the student and faculty ta...

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va...

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

Example of table literal - sql, Example of Table Literal - SQL Exampl...

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Forall statement - syntax, FORALL Statement The FORALL statements instr...

FORALL Statement The FORALL statements instruct the PL/SQL engine to bulk-bind the input collections before sending them to the SQL engine. Though the FORALL statement consists

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Explicit cursors, Explicit Cursors The set of rows returned by the que...

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

Effects of null for multiple assignments - sql, Effects of NULL for Multipl...

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Features of pl/sql, Main features of PL/SQL A good way to get familiar ...

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd