Positional vs. named parameter passing

555928

Is named parameter passing always better than positional when calling a PL/SQL stored procedure? What are the benefits and pitfalls of each method. Are there instances where you prefer one over the other?

564484

Hi Roger,

I personally prefer named notation due to its much enhanced clarity. It greatly helps a subsequent developer by not forcing him/her to look up the spec of the procedure or function each time they see it referenced in the code which calls it (I have a terrible short-term memory - so this is particularly frustrating to me).

Additionally, if some whacko comes by and changes the order (but not the names) of the parameters to the referenced procedure or function - named notation protects you from having to change your code - with positional notation - you MUST change your code.

Of course, named notation is not supported in normal SQL which calls a function, only positional is. But for PL/SQL I say use named notation all the way.

I've never tested a comparison of performance, but I would take an educated guess that it is roughly equivalent (within a few microseconds) between the approaches.

One example I think will show you the benefits of named over positional is a call to DBMS_STATS.GATHER_TABLE_STATS. This packaged procedure is used all of the time, but it has a large number of arguments. Changing a call to it can be challenging with positional notation if you don't remember the parameter order.

Here's the named notation call:

BEGIN

   DBMS_STATS.gather_table_stats (ownname               => 'SCOTT'

                                , tabname               => 'EMP'

                                , partname              => NULL

                                , estimate_percent      => DBMS_STATS.auto_sample_size

                                , block_sample          => FALSE

                                , method_opt            => 'FOR ALL INDEXED COLUMNS SIZE 254'

                                , DEGREE                => NULL

                                , granularity           => 'ALL'

                                , CASCADE               => TRUE

                                , no_invalidate         => FALSE

                                 );

END;

/

Here's the positional notation call:

BEGIN

   DBMS_STATS.gather_table_stats ('SCOTT'

                                , 'EMP'

                                , NULL

                                , DBMS_STATS.auto_sample_size

                                , FALSE

                                , 'FOR ALL INDEXED COLUMNS SIZE 254'

                                , NULL

                                , 'ALL'

                                , TRUE

                                , FALSE

                                 );

END;

/

I strongly prefer to support code with the first example over the 2nd.

Hope this helps...

Message was edited by:
PDaddy

Message was edited by:
PDaddy

555928

Thank you. This was very helpful.