Establish Clear Variable Naming Conventions

Database administrators have long insisted on, and usually enforced, strict naming conventions for tables, columns, and other database objects. The advantage of these conventions is clear: at a single glance anyone who knows the conventions (and even many who do not) will understand the type of data contained in that column or table.

If conventions for columns make sense in the database, they also make sense in PL/SQL programs and in the development tools, such as Oracle Forms. In general we recommend that you follow the same guidelines for naming variables that you follow for naming columns in your development environment. In many cases your variables simply represent database values, having been fetched from a table into those variables via a cursor. Beyond these database-sourced variables, however, there are a number of types of variables that you will use again and again in your code.

Generally, try to come up with a variable name suffix that will help identify the representative data. This convention not only limits the need for additional comments, it can also improve the quality of code, because the variable type often implies rules for its use. If you can identify the variable type by its name, then you are more likely to use the variable properly.

Below is a list of variable types and suggested naming conventions for each.

Module Parameter

A parameter has one of three modes: IN, OUT, or IN OUT. Additionally, these are special variables; so prefixing them with a "p" makes it easier to understand the meaning of these variables in the code. Attach the parameter mode as a suffix or prefix to the parameter name so that you can easily identify how that parameter should be used in the program. Here are some examples:

PROCEDURE calc_sales
   (p_company_id_IN    IN NUMBER,
    p_in_call_type     IN VARCHAR2,
    p_company_nm_INOUT IN OUT VARCHAR2)

In addition to making the meaning clear, the prefix "p" also helps differentiate the variables from the names of columns in the code, if they happen to be the same.

Program Variables

Program variables are used as placeholders for the program execution. The examples could be a placeholder to hold intermediate values in the computation, holding of values fetched from columns and so on. There are two types of program variables – local and global. There is no difference between the two, unless the same name is used in both contexts. Here is an example:

begin
  FOR emp_indx IN 1 .. 3 LOOP
    dbms_output.put_line ('this is the global variable '||emp_indx);
    FOR emp_indx IN 100 .. 103 LOOP
       dbms_output.put_line ('this is the local variable '||emp_indx);
    END LOOP;
END LOOP;
end;

Here is the output:

this is the global variable 1
this is the local variable 100
this is the local variable 101
this is the local variable 102
this is the local variable 103
this is the global variable 2
this is the local variable 100
this is the local variable 101
this is the local variable 102
this is the local variable 103
this is the global variable 3
this is the local variable 100
this is the local variable 101
this is the local variable 102
this is the local variable 103

Note, how the value of the variable emp_indx changes, depending on which part of the loop it is being referenced. To avoid confusion, employ a simple strategy to name variables differently, e.g. prefix local variables with "l" (the letter L) and global variables with "g".

Package Variables

When two variables are defined inside the package – one in the package and one inside the procedures, the precedence of the variables may be conrary to what you expected. Consider the following code:

  1  CREATE OR REPLACE PACKAGE mypkg
  2  IS
  3     PROCEDURE wrapper;
  4  END;
  5  /
  6  CREATE OR REPLACE PACKAGE BODY mypkg
  7  AS
  8     v_empid   NUMBER (3) := 100;
  9     PROCEDURE myproc
 10     IS
 11        v_empid   NUMBER (3);
 12     BEGIN
 13        v_empid := 10;
 14        DBMS_OUTPUT.put_line ('Inside myproc(). v_empid = ' || v_empid);
 15     END;
 16     PROCEDURE wrapper
 17     IS
 18     BEGIN
 19        myproc;
 20        DBMS_OUTPUT.put_line ('Outside myproc(). v_empid = ' || v_empid);
 21     END;
 22* END;

Note: There are two different variables defined in the same name - v_empid; on line 8 and 11. These two have been assigned two different values. When you execute the procedure in the package, which value will be shown?

SQL> begin mypkg.wrapper; end;
  2  /

Inside myproc(). v_empid = 10
Outside myproc(). v_empid = 100

The value that was defined in the package is the one that took precedence, even though the variable was referenced just prior to the call to the variable. This could potentially introduce several bugs into your program. Instead, follow a simple approach of naming the variables with a meaningful prefix, e.g. "l" for local (within the procedure) and "g" for global (outside any procedure of the package).

Here is the modified code with the appropriate names of the variables:

  1  CREATE OR REPLACE PACKAGE mypkg
  2  IS
  3     PROCEDURE wrapper;
  4  END;
  5  /
  6  CREATE OR REPLACE PACKAGE BODY mypkg
  7  AS
  8     g_empid   NUMBER (3) := 100;
  9     PROCEDURE myproc
 10     IS
 11        l_empid   NUMBER (3);
 12     BEGIN
 13        l_empid := 10;
 14        DBMS_OUTPUT.put_line ('Inside myproc(). l_empid = ' || l_empid);
 15     END;
 16     PROCEDURE wrapper
 17     IS
 18     BEGIN
 19        myproc;
 20        DBMS_OUTPUT.put_line ('Outside myproc(). g_empid = ' || g_empid);
 21     END;
 22* END;

In this code, there is no confusion about what variable takes precedence.

Package Global Variables

Global variables are used to store a value that is visible throughout the session. The variable value is set once and unless reset to a new value, the original value is seen by any program code in the session. To make sure you do not mix these variables in your normal processing, you should prefix or suffix the term "gv" to it. Here is an example:

SQL> begin mypkg.gv_empid := 5; end;
  2> /

SQL> begin dbms_output.put_line ('mypkg.gv_empid='||mypkg.gv_empid); end;
  2> /

mypkg.gv_empid=5

The practice of suffixing the global variables by "gv_" makes it possible to use this variable anywhere in the package as a true global variable. It can be set and referenced at any place inside the package – inside procedures or in the main body of the package. Here is an example:

CREATE OR REPLACE PACKAGE BODY mypkg
AS
   g_empid   NUMBER (3) := 100;
   gv_debug  VARCHAR2(1) := 'Y';

   PROCEDURE myproc
   IS
      l_empid   NUMBER (3);
   BEGIN
      l_empid := 10;
      IF (gv_debug = 'Y') THEN
         DBMS_OUTPUT.put_line ('Inside myproc(). l_empid = ' || l_empid);
      END IF;
   END;

In this example, you can use the variable gv_debug anywhere in the package to mean if the statements should be debugged or not.

Cursor

Append a suffix of _cur or _cursor to the cursor name, as in:

CURSOR company_cur  IS ... ;
CURSOR top_sellers_cursor IS ... ;

In case of implicit cursors, use the naming convention that implies the cursor nature. Since implicit cursors are both cursors and records, you may prefix or suffix the word "crec" to denote the variables, as shown in the example below:

FOR company_crec in (
   select …
   from company) LOOP

Record Based on Table or Cursor

These records are defined from the structure of a table or cursor. Unless more variation is needed, the simplest naming convention for a record is the name of the table or cursor with a _rec or _record suffix. For example, if the cursor is company_cur, then a record based on that cursor would be called company_rec. If you have more than one record declared for a single cursor, preface the record name with a word that describes it, such as newest_company_rec and duplicate_company_rec.

FOR Loop Index

There are two kinds of FOR loops, numeric and cursor, each with a corresponding numeric or record loop index. In a numeric loop you should incorporate the word "index" or "counter" or some similar suffix into the name of the loop index, such as:

FOR year_index IN 1 .. 12

In a cursor loop, the name of the record which serves as a loop index should follow the convention described above for records.

FOR emp_rec IN emp_cur

Named Constant

A named constant cannot be changed after it gets its default value at the time of declaration. A common convention for such constants is to prefix the name with a c:

c_last_date CONSTANT DATE := SYSDATE:

This way a programmer is less likely to try to use the constant in an inappropriate manner.

PL/SQL Table TYPE

In PL/SQL Version 2 you can create PL/SQL tables, which are similar to one-dimensional arrays. In order to create a PL/SQL table, you must first execute a TYPE declaration to create a table datatype with the right structure. We suggest that you use the suffix _tabtype to indicate that this is not actually a PL/SQL table, but a type of table. Here are some examples:

TYPE emp_names_tabtype IS TABLE OF ...;
TYPE dates_tabtype IS TABLE OF ...;

PL/SQL Table

A PL/SQL table is declared based on a table TYPE statement, as indicated above. In most situations, use the same name as the table type for the table, but leave off the type part of the suffix. The following examples correspond to the previous table types:

emp_names_tab emp_names_tabtype;
dates_tab     dates_tabtype;

You could also use the full table suffix for a high degree of clarity:

emp_names_table emp_names_tabtype;
dates_table     dates_tabtype;

If you want to minimize confusion between PL/SQL tables and database tables, it is possible to call these constructs "arrays". In this case, switch your prefix:

emp_names_array emp_names_tabtype;
dates_array     dates_tabtype;

Programmer-Defined Subtype

In PL/SQL Version 2.1 you can define subtypes from base datatypes. Use a subtype suffix to make the meaning of the variable clear. Examples:

SUBTYPE primary_key_subtype  IS BINARY_INTEGER;
SUBTYPE large_string_subtype IS VARCHAR2;

Programmer-Defined TYPE for Record

In PL/SQL Version 2 you can create records with a structure you specify (rather than from a table or cursor). To do this, you must declare a type of record which determines the structure (number and types of columns). Use a rectype prefix in the name of the TYPE declaration as follows:

TYPE sales_rectype IS RECORD ... ;

Programmer-Defined Record Instance

Once you have defined a record type, you can declare actual records with that structure. Now you can drop the type part of the rectype prefix; the naming convention for these programmer-defined records is the same as that for records based on tables and cursors:

sales_rec sales_rectype;