Oracle named parameters in regular functions

Question

Can you use named parameters in regular oracle functions like for instance REGEXP_REPLACE ? I like the named parameters notation and always use it to call self defined pieces of PL/SQL code, but the following doesn't seem to work :

select regexp_replace( string => 'TechOnTheNet'
                     , pattern => 'a|e|i|o|u'
                     , replacement_string => 'Z'
                     , start_position => 1
                     , nth_appearance => 2
                     , match_parameter => 'i') from dual;

Answer 1

Not for built in functions called from SQL; but you can from PL/SQL with assignment (and the right formal parameter names):

declare
  x varchar2(30);
begin
  x := regexp_replace( srcstr => 'TechOnTheNet'
                     , pattern => 'a|e|i|o|u'
                     , replacestr => 'Z'
                     , position => 1
                     , occurrence => 2
                     , modifier => 'i');
end;
/

TechZnTheNet

PL/SQL procedure successfully completed.

Even there you can't select the function result directly.

There is a workaround but it's a bit messy. The PL/SQL assignment is using the STANDARD package version of the function, so if you're using a function that is available from PL/SQL you could call that instead:

select sys.standard.regexp_replace( srcstr => 'TechOnTheNet'
                                  , pattern => 'a|e|i|o|u'
                                  , replacestr => 'Z'
                                  , position => 1
                                  , occurrence => 2
                                  , modifier => 'i') from dual;

SYS.STANDARD.REGEXP_REPLACE(SRCSTR=>'TECHONTHENET',PATTERN=>'A|E|I|O|U',REPLACES
--------------------------------------------------------------------------------
TechZnTheNet

As well as being longer, it's possible you might see different performance between the two versions - I don't know if it's safe to assume they're ultimately implemented the same.

You can see the available functions with a simple query like:

select object_name, position, argument_name, in_out, data_type
from all_arguments
where owner = 'SYS' and package_name = 'STANDARD'
order by object_name, overload, position;

For regexp_replace that shows the three versions of the function that are available for different argument data types. (An unnamed argument in position 0 is the function return type).