Thursday, April 22, 2010

PL/SQL Naming Conventions

The same naming conventions apply to all PL/SQL program items 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, using the remote access indicator (@), you reference the database link newyork because the 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 schema objects such as tables, sequences, views, standalone subprograms, packages, and object types. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged subprograms.

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. In the following example, the second declaration is not allowed:

DECLARE

valid_id BOOLEAN;

valid_id VARCHAR2(5); -- not allowed duplicate identifier



For the scoping rules that apply to identifiers, see "Scope and Visibility of PL/SQL Identifiers".

Case Sensitivity

Like all 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

Name Resolution

In potentially ambiguous SQL statements, the names of database 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

<
>

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;

...