Use named and mixed notation from SQL

This entry is part 5 of 8 in the series New Pl/Sql features 11g

For Pl/SQL program invocations it has named & mixed has been around for quite some time.However it you attempted the same function call from SQL prior to Oracle 11g this would lead to an error.

With Oracle 11g you can now use named & mixed notation when you call a function from SQL. This makes it much easier to call a function with lots of formal parameters of which you only want to specify 1 or 2 parameters

Let’s look at an example function

If this function is called using a mixed notation in 10g it results in an error


sql> select mixit from dual;
MIXIT()
----------------------
3

sql> select mixit(arg3=>0) from dual;
Error starting at line 19 in command:
select mixit(arg3=>0) from dual
Error at Command Line:19 Column:17
Error report:
SQL Error: ORA-00907: Ontbrekend rechterhaakje.
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

If the same code is run in an 11g environment is works just fine


sql> select mixit from dual;
MIXIT()
----------------------
3


sql> select mixit(arg3=>0) from dual;

MIXIT(ARG3=>0)
----------------------
1

Series Navigation<< Dynamic SQL EnhancementsPL/SQL Function Result Cache >>

Posted in Oracle, Pl/Sql Tagged with: , ,

Leave a Reply