Use Named Parameters in SQL Function Calls in Oracle 11g

By: Richard Niemiec


When passing formal parameter values to PL/SQL subprograms, Oracle has always permitted position, named, and mixed notations. It is generally an accepted best practice to use the named notation, as it both increases code readability and offers a level of protection against changes to a subprogram’s signature. Unfortunately, prior to 11g, the named notation was not available when PL/SQL functions were called from within a SQL command. This limitation left PL/SQL function calls that were embedded in SQL statements vulnerable to undesired effects if the subroutine signatures were not changed in a very careful manner. Let’s look at an example. First, I create a function that accepts a person’s first and last names and formats them into a single string using the format LastName, FirstName:

 

0570_002

 

 

 

 

I’ll now use this function to format a couple of fictitious records from an EMPLOYEES table. I start out by using the positional notation that was mandatory prior to 11g:

 

0570_003

 

 

 

 

 

 

 

For comparison purposes, I’ll employ the same data using a PL/SQL block, as that method has always offered the use of named notation:

 

0571_001

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As expected, the results are identical to what was returned by the SQL statement. Now let’s fast-forward to a future enhancement in which the FORMAT_NAME function supports the middle name as well:

 

0571_002

 

 

 

 

 

Note that the developer interjected the new middle name parameter between the existing parameters. Reexecuting the SQL and PL/SQL extraction commands from before, you see that the former is returning incorrect results because of the positional notation:

 

0572_002

 

 

 

 

 

 

 

 

With 11g, you can now utilize the named notation with SQL to limit the impact of function signature changes, as shown here:

 

0572_002

 

 

 

 

 

 

 

TIP

Named notation continues to be the preferred method for passing parameters to subroutines. With 11g, you can now extend this best practice approach to SQL statements to yield consistency across the entire code base.

Leave a Reply