Variable declared in blocks such as subprograms or anonymous blocks can be accessed from the executable section or the exception section of other blocks depending upon their relative location.
Accessing a variable means being able to reference it within a SQL statement or an SPL statement as is done with any local variable.
Note
If the subprogram signature contains formal parameters, these may be accessed in the same manner as local variables of the subprogram. In this section, all discussion related to variables of a subprogram also applies to formal parameters of the subprogram.
Access of variables not only includes those defined as a data type, but also includes others such as record types, collection types, and cursors.
The variable may be accessed by at most one qualifier, which is the name of the subprogram or labeled anonymous block in which the variable has been locally declared.
The syntax to reference a variable is shown by the following:
If specified, qualifier is the subprogram or labeled anonymous block in which variable has been declared in its declaration section (that is, it is a local variable).
Note
In Advanced Server, there is only one circumstance where two qualifiers are permitted. This scenario is for accessing public variables of packages where the reference can be specified in the following format:
For more information about supported package syntax, see the Database Compatibility for Oracle Developers Built-In Package Guide.
The following summarizes how variables can be accessed:
Variables can be accessed as long as the block in which the variable has been locally declared is within the ancestor hierarchical path starting from the block containing the reference to the variable. Such variables declared in ancestor blocks are referred to as global variables.
If a reference to an unqualified variable is made, the first attempt is to locate a local variable of that name. If such a local variable does not exist, then the search for the variable is made in the parent of the current block, and so forth, proceeding up the ancestor hierarchy. If such a variable is not found, then an error occurs upon invocation of the subprogram.
If a reference to a qualified variable is made, the same search process is performed as described in the previous bullet point, but searching for the first match of the subprogram or labeled anonymous block that contains the local variable. The search proceeds up the ancestor hierarchy until a match is found. If such a match is not found, then an error occurs upon invocation of the subprogram.
The following location of variables cannot be accessed relative to the block from where the reference to the variable is made:
Variables declared in a descendent block cannot be accessed,
Variables declared in a sibling block, a sibling block of an ancestor block, or any descendants within the sibling block cannot be accessed.
Note
The Advanced Server process for accessing variables is not compatible with Oracle databases. For Oracle, any number of qualifiers can be specified and the search is based upon the first match of the first qualifier in a similar manner to the Oracle matching algorithm for invoking subprograms.
The following example displays how variables in various blocks are accessed, with and without qualifiers. The lines that are commented out illustrate attempts to access variables that would result in an error.
The following is the output showing the content of each variable when the procedure is invoked:
The following example shows similar access attempts when all variables in all blocks have the same name:
The following is the output showing the content of each variable when the procedure is invoked:
As previously discussed, the labels on anonymous blocks can also be used to qualify access to variables. The following example shows variable access within a set of nested anonymous blocks:
The following is the output showing the content of each variable when the anonymous block is invoked:
The following example is an object type whose object type method, display_emp, contains record type emp_typ and subprocedure emp_sal_query. Record variable r_emp declared locally to emp_sal_query is able to access the record type emp_typ declared in the parent block display_emp.
The following is the output displayed when an instance of the object type is created and procedure display_emp is invoked:
The following example is a package with three levels of subprocedures. A record type, collection type, and cursor type declared in the upper level procedure can be accessed by the descendent subprocedure.
The following is the output displayed when the top level package procedure is invoked: