

PL/SQL Call Notation Tips
Oracle Tips

PL/SQL Named Notation standards
Procedures and functions should always be called using named notation for
their parameters. This helps identify the data that is being passed to the
stored PL/SQL object (assuming that the identifiers chosen for the parameters
are meaningful). Place only one parameter on each line of the call:
DaysBetween (dStartDate => dEnrolledDate,
dEndDate => dGraduationDate,
nGPA => nFinalGPA,
nDaysBetween => nDuration);
There are multiple methods of associating the actual parameters with the formal parameters during a subprogram call. And they are,
PL/SQL Positional Notation
The process of associating the actual parameter implicitly by considering the position of the formal parameters in the subprogram's header is called as the positional notation. With the positional notation, the PL/SQL compiler associates the first actual parameter from the subprogram call with the first formal parameter of the subprogram, second actual parameter from the subprogram call with the second formal parameter of the subprogram and so on.
The function in the above example can be called using the positional notation as like below,
DECLARE
l_vc_dept VARCHAR2(30);
l_n_sal NUMBER;
BEGIN
l_n_sal:=func_emp(7815,'John','13June2011',l_vc_dept);
dbms_output.put_line('The Employee''s dept is 'l_vc_dept);
dbms_output.put_line('The Employee''s sal is 'l_n_sal);
END;
/
In the above example, the actual parameters 7815, 'John', '13June2011' and the variable l_vc_dept are in the exact order of their formal parameters declared in the function header.
PL/SQL Named Notation
The process of associating the actual parameter explicitly by the formal parameter's name in the subprogram's header is called as the positional notation. The combination symbol => is used for combining the formal and the actual parameters during the named notation call.
The prototype for the named notation call of a procedure is as below,
Procedure_name(formal_parameter1=>actual_parameter1,formal_parameter2=>actual_parameter2,..);
As the name of the formal parameters are provided explicitly, the PL/SQL compiler does not have to rely on the order of the parameters to make the association between the formal and the actual parameters.
The function in the above example can be called using the named notation as like below. Here the order of the parameters are shuffled but the function identifies the parameters using their named notation.
DECLARE
l_vc_dept VARCHAR2(30);
l_n_sal NUMBER;
BEGIN
l_n_sal:=func_emp(emp_name=>'John',emp_id=>7815,emp_dept=>l_vc_dept,emp_join_date=>'13June2011');
dbms_output.put_line('The Employee''s dept is 'l_vc_dept);
dbms_output.put_line('The Employee''s sal is 'l_n_sal);
END;
/
Mixed Notation
The process of using both the positional notation and the named notation together is called as mixed notation. The important rule to keep in mind while performing this type of notation is that the positional notations should be used before the usage of the named notation. If a parameter is populated using the named notation, all subsequent parameters must also be populated using the named notation.
The function in the above example can be called using the mixed notation as like below. In the below example, the first two parameters are populated using the positional notation and the rest parameters are populated using the named notation.
DECLARE
l_vc_dept VARCHAR2(30);
l_n_sal NUMBER;
BEGIN
l_n_sal:=func_emp(7815,'John',emp_join_date=>'13June2011',emp_dept=>l_vc_dept);
dbms_output.put_line('The Employee''s dept is 'l_vc_dept);
dbms_output.put_line('The Employee''s sal is 'l_n_sal);
END;
/
The parameters using for the population can be skipped if it is not a mandatory one (by defaulting the parameter to some value during the subprogram creation).