Skip to main content

Use named notation for crystal clear code

The first and most important criteria of high quality code is that it meets user requirements (it's "correct").

The second most important criteria is that it fast enough to meet user needs. It doesn't matter that you might be able to make it go faster.

The third most important criteria of excellent code is that it is maintainable. Why, you might be wondering, is maintainabiliity so important?

Most of the code we write, especially code that lives in the database, sticks around for a long time. Well past the point when you were familiar with the code you wrote. And likely past the time when you are still working on that same code or project.

So the overall cost of development of that code is not just the initial build expense, but also must include the cost of maintaining that code. All too often we are so absorbed in meeting deadlines that we sacrifice clarity and readability to "get the job done."

And then we - or others - pay the price later. Conversely, if we take advantage of features in PL/SQL that enhance readability of code, the burden of maintaining it later will be reduced. Hey, it might even make it easier for you to debug your code today!

With named notation, you explicitly associate the formal parameter (the name of the parameter) with the actual parameter (the value of the parameter) right in the call to the program, using the combination symbol =>.

The general syntax for named notation is:
formal_parameter_name => argument_value 
Because you provide the name of the formal parameter explicitly, PL/SQL no longer needs to rely on the order of the parameters to make the association from actual to formal. So, if you use named notation, you do not need to list the parameters in your call to the program in the same order as the formal parameters in the header.

Suppose I define a function as follows:
FUNCTION total_sales
   (company_id_in IN company.company_id%TYPE,
    status_in IN order.status_code%TYPE := NULL)
RETURN std_types.dollar_amount;

Then I can call total_sales in either of these two ways:
new_sales :=
   total_sales (company_id_in => order_pkg.company_id, status_in =>'N');

 new_sales :=
   total_sales (status_in =>'N', company_id_in => order_pkg.company_id);
It is also possible to mix named and positional notation in the same program call, as in:
l_new_sales := total_sales (order_pkg.company_id, status_in =>'N');
If you do mix notation, however, you must list all of your positional parameters before any named notation parameters, as shown in the above code. After all, positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter. If you place named notation parameters in front of positional notation, PL/SQL loses its place.

Both of the following calls to total_sales will fail. The first statement fails because the named notation comes first. The second fails because positional notation is used, but the parameters are in the wrong order. PL/SQL will try to convert ‘N’ to a NUMBER (for company_id):
l_new_sales := total_sales (company_id_in => order_pkg.company_id, 'N');
l_new_sales := total_sales ('N', company_id_in => order_pkg.company_id);

Benefits of named notation

Now that you are aware of the different ways to notate the order and association of parameters, you might be wondering why you would ever use named notation. Here are two possibilities:

1. Named notation is self-documenting

When you use named notation, the call to the program clearly describes the formal parameter to which the actual parameter is assigned. The names of formal parameters can and should be designed so that their purpose is self-explanatory. In a way, the descriptive aspect of named notation is another form of program documentation. If you are not familiar with all of the modules called by an application, the listing of the formal parameters helps reinforce your understanding of a particular program call. In some development environments, the standard for parameter notation is named notation for just this reason. This is especially true when the formal parameters are named following the convention of appending the passing mode as the last token. Then, the direction of data can be clearly seen simply by investigating the procedure or function call.

2. Named notation gives you complete flexibility over parameter specification

You can list the parameters in any order you want. (This does not mean, however, that you should randomly order your arguments when you call a program!) You can also include only the parameters you want or need in the parameter list. Complex applications may at times require procedures with literally dozens of parameters. Any parameter with a default value can be left out of the call to the procedure. Using named notation, the developer can use the procedure by passing only the values needed for that usage.

Let’s see how these benefits can be applied. Consider the following program header:
PROCEDURE so_many_parameters (
   advertising_budget_in   IN     NUMBER
 , contributions_inout     IN OUT NUMBER
 , start_date_in           IN     DATE DEFAULT SYSDATE
 , bonus_out                  OUT NUMBER
 , flags_in                IN     VARCHAR2 DEFAULT 'WHENEVER POSSIBLE'
An analysis of the parameter list yields these observations:
  • The minimum number of arguments that must be passed to so_many_parameters is three. To determine this, add the number of IN parameters without default values to the number of OUT or IN OUT parameters.
  • I can call this program with positional notation with either four or five arguments, because the last parameter has mode IN with a default value.
  • You will need at least two variables to hold the values returned by the OUT and IN OUT parameters.
  • Given this parameter list, there are a number of ways that you can call this program:
All positional notation, all actual parameters specified. Notice how difficult it is to recall the parameter (and significance) of each of these values.
   l_max_bonus       NUMBER;
   l_mucho_dollars   NUMBER := 100000;
   /* All positional notation */
   so_many_parameters (50000000
                    , l_mucho_dollars
                    , SYSDATE + 20
                    , l_max_bonus
                    , 'PAY OFF OSHA'
All positional notation, minimum number of actual parameters specified. Still hard to understand.
so_many_parameters (50000000
                , l_mucho_dollars
                , SYSDATE + 20
                , l_max_bonus
All named notation, keeping the original order intact. Now my call to so_many_parameters is self-documenting.
  (advertising_budget_in  => 50000000
 , contributions_inout    => l_mucho_dollars
 , start_date_in          => SYSDATE
 , bonus_out              => l_max_bonus
 , flags_in               => 'autonomous,plsql,sql'
Skip over all IN parameters with default values, another critical feature of named notation:
   (advertising_budget_in  => 50000000
  , contributions_inout    => l_mucho_dollars
  , bonus_out              => l_max_bonus
Change the order in which actual parameters are specified with named notation; also provide just a partial list:
  (bonus_out             => l_max_bonus
 , start_date_in         => SYSDATE
 , advertising_budget_in => 50000000
 , contributions_inout   => l_mucho_dollars
Blend positional and named notation. You can start with positional, but once you switch to named notation, you can’t go back to positional.
   , l_mucho_dollars
   , start_date_in  => SYSDATE
   , bonus_out      => l_max_bonus
As you can see, there is a lot of flexibility when it comes to passing arguments to a parameter list in PL/SQL. As a general rule, named notation is the best way to write code that is readable and more easily maintained. You just have to take the time to look up and write the parameter names.


Demonstration of Named Notation -my Oracle LiveSQL script

Calling Notation For PL/SQL Subroutines by Manish Sharma

Thanks, O'Reilly Media!

Portions of this post have been lifted (gently, carefully, lovingly) from Oracle PL/SQL Programming, with permission from O'Reilly Media.


Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…