PL/SQL User's Guide and Reference

Naming Conventions

The same naming conventions apply to all PL/SQL program objects and units including constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example, you might use the procedure name raise_salary in any of the following ways:

raise_salary(...);                      -- simple
emp_actions.raise_salary(...);          -- qualified
raise_salary@newyork(...);              -- remote
emp_actions.raise_salary@newyork(...);  -- qualified and remote

In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called emp_actions. In the third case, you reference the database link newyork because the (standalone) procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.

Synonyms

You can create synonyms to provide location transparency for remote database objects such as tables, sequences, views, standalone subprograms, and packages. However, you cannot create synonyms for objects declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged procedures.

Scoping

Within the same scope, all declared identifiers must be unique. So, even if their datatypes differ, variables and parameters cannot share the same name. For example, two of the following declarations are illegal:

DECLARE
   valid_id BOOLEAN;
   valid_id VARCHAR2(5);  -- illegal duplicate identifier
   FUNCTION bonus (valid_id IN INTEGER) RETURN REAL IS ...
                          -- illegal triplicate identifier

For the scoping rules that apply to identifiers, see "Scope and Visibility" .

Case Sensitivity

Like other identifiers, the names of constants, variables, and parameters are not case sensitive. For instance, PL/SQL considers the following names to be the same:

DECLARE
   zip_code INTEGER;
   Zip_Code INTEGER;  -- same as zip_code
   ZIP_CODE INTEGER;  -- same as zip_code and Zip_Code

Name Resolution

In potentially ambiguous SQL statements, the names of local variables and formal parameters take precedence over the names of database tables. For example, the following UPDATE statement fails because PL/SQL assumes that emp refers to the loop counter:

FOR emp IN 1..5 LOOP
   ...
   UPDATE emp SET bonus = 500 WHERE ...
END LOOP;

Likewise, the following SELECT statement fails because PL/SQL assumes that emp refers to the formal parameter:

PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS
   avg_sal REAL;
BEGIN
   SELECT AVG(sal) INTO avg_sal FROM emp WHERE ...

In such cases, you can prefix the table name with a username, as follows, but it is better programming practice to rename the variable or formal parameter.:

PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS
   avg_sal REAL;
BEGIN
   SELECT AVG(sal) INTO avg_sal FROM scott.emp WHERE ...

Unlike the names of tables, the names of columns take precedence over the names of local variables and formal parameters. For example, the following DELETE statement removes all employees from the emp table, not just KING, because Oracle assumes that both enames in the WHERE clause refer to the database column:

DECLARE
   ename VARCHAR2(10) := 'KING';
BEGIN
   DELETE FROM emp WHERE ename = ename;

In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with my_, as follows:

DECLARE
   my_ename VARCHAR2(10);

Or, use a block label to qualify references, as in

<<main>>
DECLARE
   ename VARCHAR2(10) := 'KING';
BEGIN
   DELETE FROM emp WHERE ename = main.ename;

The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:

FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS
   job CHAR(10);
BEGIN
   ...
   SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;
                -- refers to formal parameter and local variable

For a full discussion of name resolution, see Oracle7 Server Application Developer's Guide.