Skip to main content

Referencing package-level variables inside the package body

I received this question yesterday:
Is there a shortcut for referencing package variables in the package body? In Java, as an example, you can use the "this" keyword as a reference to the current object. This came about as I needed to create a copy of a package for debug purposes and realized I had to rename all the references to the package name within the package body.
Before I answer, let's look at an example of what Tony is talking about. I create a package specification and then a body with package-level variable (declared outside any subprogram of the package):

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE proc (n_in IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < pkg.smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Notice the line in blue and bold. I reference the package level variable, qualified with the package name (the approach that Tony has taken).

But then when Tony changes the name of the package, so he also has to change the dot-qualified references within the package.

Can you use "this" syntax, as is available in Java? No. You will see this error when trying to compile the package body:

PLS-00201: identifier 'THIS.SMALLEST' must be declared

"this"-style syntax is used only with object types in Oracle Database (our object-oriented extensions to the relational model), in which case you use "SELF" to reference the current object type instance.

So what should Tony do? Here are his (and mine, and your) options:
  • Continue to dot qualify with the package name, and do a (careful, very careful) global search and replace of the old package name to new. Like I say: CAREFUL.
  • Remove the package name. You don't need it.
That's right. You don't need to qualify the reference to this variable. In other words, this version of he package body will compile just fine:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The compiler will try to resolve the reference to smallest inside proc. When that doesn't work, it will check the "next level up" in scope: the package body. Ah ha! There it is. And the body compiles.

Of course, this will not work as desired if you declare a variable or constant with the same name inside proc. The following version will also compile, but that reference to smaller inside proc will not refer to the package level variable.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

In this case, you must qualify the variable with the scope you mean. You could qualify with the subprogram (procedure) or package name, by the way:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      smallest NUMBER;
   BEGIN
      IF n_in < proc.smallest -- references procedure's smallest
      THEN

      IF n_in < pkg.smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

Of course, best of all is to avoid declaring variables with the same names at different scopes. That can be confusing - and then requires the use of dot-qualification. Another approach, frequently used, is to use different naming conventions for variables of different scope. For example, I commonly use "g_" to indicate a global variable in a package, and "l_" for local variables.

So my version of the package body would look like:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_smallest NUMBER;
   BEGIN
      IF n_in < l_smallest -- references procedure's smallest
      THEN

      IF n_in < g_smallest -- references package level smallest
      THEN
         DBMS_OUTPUT.put_line ('too small');
      END IF;
   END;
END;
/

The most important thing is to be consistent in your approach so others (including the Future You) can more easily understand your code.

A final comment regarding dot-qualifying variable names: when you reference PL/SQL variables and constants inside SQL statements in your PL/SQL blocks, you should always qualify them with their scope names. Example:

CREATE OR REPLACE PACKAGE BODY pkg
IS
   g_smallest   NUMBER;

   PROCEDURE proc (n_in IN NUMBER)
   IS
      l_salary NUMBER;
   BEGIN
      SELECT salary INTO l_salary
        FROM employees e
       WHERE e.employee_id = proc.n_in;
   END;
END;
/

By doing this, you give more information to the compiler that it can use to reduce the times when a program unit needs to be invalidated and recompiled. In the above package body, if I did not qualify my reference to n_in inside the SELECT, adding a column named "n_in" will cause the package body to be set to INVALID.

And upon recompilation, well, that reference to "n_in" will now be to the column and not your variable. Not good. By fully qualifying the reference, however, the compiler knows (via fine-grained dependency management, added in 11.1) that there can be no confusion, and adding a column to the employees table could not possibly disrupt the current behavior of the package body.

Comments

  1. I dislike using variables/parameters directly in DML statements like this for the similar reason of that _I_ dont necessarily know if "n_in" refers to a column or a variable. I had never considered what would happen if you added a new column to a table with that name..

    Instead, if possible I try to use an explicit cursor with cursor parameters.

    ReplyDelete
    Replies
    1. David, I don't think it is a good idea to choose the type of SQL access you are writing in your code to avoid un-intended name capture.

      Instead, as I show above, you qualify *all* references inside the SQL statement - via table aliases and PL/SQL program / block name, and then you are 100% protected.

      Delete
    2. perhaps i phrased that badly..

      I meant that if i end up with a query the needs to reference a variable, i will try to write it as an explicit cursor with parameters rather then reference the variable directly.

      Delete
    3. I got that, David. Perhaps it was I who was not as clear as could be.

      I mean that, for example, implicit cursors (SELECT-INTO) are generally faster than explicit OPEN-FETCH-CLOSE. So to pick an explicit cursor when you could fully qualify variables and have no concerns whatsoever about inappropriate name capture in the future doesn't seem to me to be a good choice.

      Delete

Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…