4.5.1 Benefits of Anchored Declarations

All the declarations you have so far seen -- character, numeric, date, Boolean -- specify explicitly the type of data for that variable. In each of these cases, the declaration contains a direct reference to a datatype and, in most cases, a constraint on that datatype. You can think of this as a kind of hardcoding in your program. While this approach to declarations is certainly valid, it can cause problems in the following situations:

• Synchronization with database columns. The PL/SQL variable "represents" database information in the program. If I declare explicitly and then change the structure of the underlying table, my program may not work properly.

• Normalization of local variables. The PL/SQL variable stores calculated values used throughout the application. What are the consequences of repeating (hardcoding) the same datatype and constraint for each declaration in all of my programs?

Let's take a look at each of these scenarios in more detail.

4.5.2 Anchoring at Compile Time

The %TYPE declaration attribute anchors the datatype of one variable to that of another data structure at the time a PL/SQL block is compiled. If a change is made to the "source" datatype, then any program which contains a declaration anchored to this datatype must be recompiled before it will be able to use this new state of the datatype.

The consequences of this rule differ for PL/SQL modules stored in the database and those defined in client-side tools, such as Oracle Forms.

Consider the following declaration of company_name in the procedure display_company:

PROCEDURE display_company (company_id_in IN INTEGER)
IS
   company_name company.name%TYPE;
BEGIN
   ...
END;

When PL/SQL compiles this module, it looks up the structure of the company table in the data dictionary, finds the column NAME, and obtains its datatype. It then uses this data dictionary-based datatype to define the new variable.

What, then, is the impact on the compiled display_company procedure if the datatype for the name column of the company table changes? There are two possibilities:

• If display_company is a stored procedure, then the compiled code will be marked as "invalid." The next time a program tries to run display_company, it will be recompiled automatically before it is used.

• If display_company is a client-side procedure, then the Oracle Server cannot mark the program as invalid. The compiled client source code remains compiled using the old datatype. The next time you execute this module, it could cause a VALUE_ERROR exception to be raised.

Whether stored or in client-side code, you should make sure that all affected modules are recompiled after data structure changes.

4.5.3 Nesting Usages of the %TYPE Attribute

You can nest usages of %TYPE in your declarations as well:

DECLARE
   /* The "base" variable */
   unlimited_revenue NUMBER;

   /* Anchored to unlimited revenue */
   total_revenue unlimited_revenue%TYPE;

   /* Anchored to total revenue */
   total_rev_94    total_revenue%TYPE;
   total_rev_95    total_revenue%TYPE;
BEGIN

In this case total_revenue is based on unlimited_revenue and both variables for 1994 and 1995 are based on the total_revenue variable. There is no practical limit on the number of layers of nested usages of %TYPE.

4.5.4 Anchoring to Variables in Other PL/SQL Blocks

The declaration of the source variable for your %TYPE declarations does not need to be in the same declaration section as the variables which use it. That variable must simply be visible in that section. The variable could be a global PL/SQL variable (defined in a package) or be defined in an PL/SQL block which contains the current block, as in the following example:

PROCEDURE calc_revenue
IS
   unlimited_revenue NUMBER;
   total_revenue unlimited_revenue%TYPE;
BEGIN
   IF TO_CHAR (SYSDATE, 'YYYY') = '1994'
   THEN
      DECLARE
         total_rev_94    total_revenue%TYPE;
      BEGIN
         ...
      END;
   END IF;
END calc_revenue;

4.5.5 Anchoring to NOT NULL Datatypes

When you declare a variable, you can also specify the need for the variable to be NOT NULL This NOT NULL declaration constraint is transferred to variables declared with the %TYPE attribute. If I include a NOT NULL in my declaration of a source variable (one that is referenced afterwards in a %TYPE declaration), I must also make sure to specify a default value for the variables which make use of that source variable. Suppose I declare max_available_date NOT NULL in the following example:

DECLARE
   max_available_date DATE NOT NULL :=
            LAST_DAY (ADD_MONTHS (SYSDATE, 3));
   last_ship_date max_available_date%TYPE;

The declaration of last_ship_date will then fail to compile, with the following message:

a variable declared NOT NULL must have an initialization assignment.

If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column.

The NOT NULL column constraint does not apply to variables declared with the %TYPE attribute. The following code will compile successfully:

DECLARE
   -- Company name is a NOT NULL column in the company table.
   comp_name company.name%TYPE;
BEGIN
   comp_name := NULL;

You will be able to declare the comp_name variable without specifying a default, and you will be able to NULL out the contents of that variable.