You can use either positional or named parameter notation when parameters are passed to a function or procedure. If you specify parameters by using positional notation, you must list the parameters in the order that they are declared. If you specify parameters with named notation, the order of the parameters is not significant.

To specify parameters using named notation, list the name of each parameter followed by an arrow (=>) and the parameter value. Named notation is more verbose, but makes your code easier to read and maintain.

A simple example that demonstrates using positional and named parameter notation is as follows:

CREATE OR REPLACE PROCEDURE emp_info (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
)
IS
BEGIN
    dbms_output.put_line('Department Number =' || p_deptno);
    dbms_output.put_line('Employee Number =' || p_empno);
    dbms_output.put_line('Employee Name =' || p_ename;
END;

To call the procedure using positional notation, pass the following parameters:

emp_info(30, 7455, 'Clark');

To call the procedure using named notation, pass the following parameters:

emp_info(p_ename =>'Clark', p_empno=>7455, p_deptno=>30);

Using named notation can alleviate the need to re-arrange a parameter list of a procedure if the parameter list changes, if the parameters are reordered, or if a new optional parameter is added.

In a case where you have a default value for an argument and the argument is not a trailing argument, you must use named notation to call the procedure or function. The following case demonstrates a procedure with two leading default arguments.

CREATE OR REPLACE PROCEDURE check_balance (
    p_customerID  IN NUMBER DEFAULT NULL,
    p_balance     IN NUMBER DEFAULT NULL,
    p_amount      IN NUMBER
)
IS
DECLARE
    balance NUMBER;
BEGIN
   IF (p_balance IS NULL AND p_customerID IS NULL) THEN
      RAISE_APPLICATION_ERROR
          (-20010, 'Must provide balance or customer');
   ELSEIF (p_balance IS NOT NULL AND p_customerID IS NOT NULL) THEN
      RAISE_APPLICATION_ERROR
          (-20020,'Must provide balance or customer, not both');
   ELSEIF (p_balance IS NULL) THEN
      balance := getCustomerBalance(p_customerID);
   ELSE
      balance := p_balance;
   END IF;

   IF (amount > balance) THEN
      RAISE_APPLICATION_ERROR
        (-20030, 'Balance insufficient');
   END IF;
END;

You can only omit non-trailing argument values (when you call this procedure) by using named notation. When using positional notation, only trailing arguments are allowed to default. You can call this procedure with the following arguments:

check_balance(p_customerID => 10, p_amount = 500.00)

check_balance(p_balance => 1000.00, p_amount = 500.00)

You can use a combination of positional and named notation (mixed notation) to specify parameters. A simple example that demonstrates how to use mixed parameter notation is as follows:

CREATE OR REPLACE PROCEDURE emp_info (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
)
IS
BEGIN
    dbms_output.put_line('Department Number =' || p_deptno);
    dbms_output.put_line('Employee Number =' || p_empno);
    dbms_output.put_line('Employee Name =' || p_ename;
END;

You can call the procedure by using mixed notation:

emp_info(30, p_ename =>'Clark', p_empno=>7455);

If you do use mixed notation, remember that named arguments cannot precede positional arguments.