Using named notations in Functions in Oracle 11g
Posted by decipherinfosys on March 4, 2008
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
WHEN P_COL3 < sysdate THEN P_COL1 + P_COL2
WHEN P_COL3 >= sysdate THEN P_COL1 – P_COL2
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;
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;
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;
Sorry, the comment form is closed at this time.