Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Positional vs. named parameter passing

555928
Member Posts: 5
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
-
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
Here's the positional notation call:
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;
/BEGIN
I strongly prefer to support code with the first example over the 2nd.
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;
/
Hope this helps...
Message was edited by:
PDaddy
Message was edited by:
PDaddy -
Thank you. This was very helpful.
This discussion has been closed.