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;
...
