Forum Stats

  • 3,827,089 Users
  • 2,260,740 Discussions
  • 7,897,163 Comments

Discussions

Positional vs. named parameter passing

555928
555928 Member Posts: 5
edited Feb 28, 2007 5:42PM in SQL & PL/SQL
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?

Comments

  • 564484
    564484 Member Posts: 106
    edited Feb 28, 2007 5:07PM
    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
    555928 Member Posts: 5
    Thank you. This was very helpful.
This discussion has been closed.