By position or by name…

In Oracle PL/SQL you have the possibility to call another program with parameters either by position or by name. Both have their pros and cons I think.

Consider the betwnstr function (posted last time) and these calls (all have the same result):

l_varchar := betwnstr('hello world' , 3 , 5 , true); l_varchar := betwnstr('hello world' , 3 , 5); l_varchar := betwnstr( string_in => 'hello world' , start_in => 3 , end_in => 5 , inclusive_in => true); l_varchar := betwnstr( string_in => 'hello world' , start_in => 3 , end_in => 5);

Which one of these calls is better? Oracle doesn’t mind. To the PL/SQL engine they are all valid and all correct. I don’t even think there is a performance issue with either one of them.

What are my thoughts on this issue:
– Calling programs with parameters by name is better because you can skip the defaulted parameters which are in between other (non defaulted) parameters.
– Calling programs with parameters by name is better because it’s more self documenting, you describe what variable goes into what parameter.
– If you rearrange the parameters, for whatever reason, your programs calling this one will still be working.

Calling programs with parameters by position is better because… well, not many pros I can think of for this approach. It’s mostly ‘old’ code I come across that calls programs with parameters by position. I always have to check the called program to see what the parameters mean.

Which one (in your opinion) is better.

3 thoughts on “By position or by name…

  1. Well, I Love By name method. I use it for all non standard functions. Unfortunately it cannot be used in SQL statements (I’ve heard it will change in 11g).
    BY position I use only for well known Oracle standard functions and in SQL.

    Paweł


  2. I also opt for “by name”. The self documentation is really worth it. It’s interesting that other programming languages do not have this concept. The code is so much more readable.

    BTW, what I have read Oracle 11g lifts the restriction that you couldn’t use “by name” when a function was called in SQL statement.

    Patrick


  3. “Calling programs with parameters by position is better because…”
    The only benefit I’ve ever experienced is when you need to rename a parameter. I had to change a routine that had a parameter with the useful name of p_date. I wanted to change the name because people were passing the wrong date to it.
    The moral of this story is…first name your parameters appropriately and THEN use named notation.


Leave a Reply

Your email address will not be published.