Migrating from 9i to 11g – Named and mixed notation for PL/SQL sub program in a sql stmt

Positional, named or mixed-notations are allowed in calls to functions / procedures. Prior to Oracle 11g only positional notation was supported from the SQL statements. From 11g named and mixed notations are supported.

If there are too many default values then we have been given values to them as well earlier – but, from now on they can be skipped and can give only the required values.

CREATE OR REPLACE function
mixed_notation (in_param1 IN NUMBER DEFAULT 10, in_param2 IN NUMBER DEFAULT 12)
RETURN NUMBER
IS
v_value number;
BEGIN
v_value := in_param1 * in_param2;
RETURN (v_value);
END mixed_notation;

Oracle 9i

SQL> select mixed_notation(10, 12) from dual;

MIXED_NOTATION(10,12)
———————
120

Oracle 11g

SQL> select mixed_notation() from dual;

MIXED_NOTATION()
—————-
120

SQL> select mixed_notation(in_param1=>6) from dual;

MIXED_NOTATION(IN_PARAM1=>6)
—————————-
72

Mix it up !!