Using named notations in Functions in Oracle

Solution:

In 11g, named notations are also available when functions are called.  There are three ways we can call a procedure or a function:
•    Position notation
•    Named notation
•    Mixed notation
Up untill oracle 10g,  functions were called using only positional notations.  In 11g, we can also call functions using named notations or mixed notation. Let us create the following function for our testing purpose.
CREATE OR REPLACE FUNCTION TEST_FUNC
(
P_COL1 IN NUMBER,
P_COL2 IN NUMBER,
P_COL3 IN DATE
)
RETURN NUMBER AS
v_final_value NUMBER;
BEGIN
SELECT (CASE
WHEN P_COL3 < sysdate THEN  P_COL1 + P_COL2
WHEN P_COL3 >= sysdate THEN P_COL1 – P_COL2
ELSE 0
END)
INTO v_final_Value
FROM DUAL;
return v_final_value;
END;
/
Once created, we will invoke the function with three different notations mentioned above.
Positional notation: It is very easy to use but it becomes difficult to debug the code when positional values are swiped or placed in the wrong order. Also we have to make sure that parameters are defined  in the same order as declared in the function.
SQL> SELECT TEST_FUNC(5,2,SYSDATE-1) FROM DUAL;
TEST_FUNC(5,2,SYSDATE-1)
————————
7
Named notation: This notation is bit more verbose than the positional notation, but gives crystal clear declaration of the parameters in the function or procedures. It specifies name/value pair of the parameter using  associative operator =>. Another major advantage of this type of declaration is that we are not worried about re-ordering of the parameters in function declaration. If somebody changes the position of the parameter, function  call remains unchanged. Named notation is new in Oracle 11g, for function call. Following is the syntax.
SQL> SELECT TEST_FUNC(p_col3=>sysdate-1,p_col1=>5,p_col2=>2) FROM DUAL;
TEST_FUNC(P_COL3=>SYSDATE-1,P_COL1=>5,P_COL2=>2)
————————————————
7
Trying to run same syntax in 10g, will result into an oracle error.
Mixed notation: As name suggests, we can use positional and named notations together. Only thing to keep in mind is to declare positional parameters first and then declare remaining parameters in named notation.  Using named notations prior to using positional notation will result into an error. Here is the example.
SQL> SELECT TEST_FUNC(p_col1=>5,2,sysdate-1) FROM DUAL;
Above shown statement will result into an error.
ORA-06553: PLS-312: a positional parameter association may not follow a named Association
Now let us use positional parameters first and then named parameters.
SQL> SELECT TEST_FUNC(5,2,p_col3=>sysdate-1) FROM DUAL;
TEST_FUNC(5,2,P_COL3=>SYSDATE-1)
——————————–
7

Thanks