Oracle PL/SQL Best Practices

Name

DAT-14: Use package globals judiciously and only in package bodies.

Synopsis

A global variable is a data structure that can be referenced outside the scope or block in which it’s declared. In the following block, for example, the l_publish_date is global to the local display_book_info procedure:

DECLARE
   l_publish_date DATE;
   ...
   PROCEDURE display_book_info IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (l_publish_date);
   END;

Globals are dangerous and should be avoided, because they create hidden “dependencies” or side-effects. A global doesn’t have to be passed through the parameter list, so it’s hard for you to even know that a global is referenced in a program without looking at the implementation.

Globals are most often defined in packages. If you declare a variable at the package level (not within any specific program), that variable exists and retains its value for the duration of your session.

The general solution to this problem is to pass the global as a parameter in your procedure and function; don’t reference it directly within the program. Another general technique to keep in mind is to declare variables, cursors, functions, and other objects as “deeply” as possible (i.e., in the block nearest to where, or within which, that object will be used), in order to reduce the chance of unintended use by other sections of the code.

Warning

Reliance on global data structures can be a particularly acute problem in Oracle Developer’s Formsbuilder (previously known as Oracle Forms). Developers have ...