By: Steven Feuerstein On: July 14, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

I’m going to assume right now that you write PL/SQL procedures and functions for your Oracle Database development. Because if not, all you write are anonymous blocks and triggers. And that would mean you have a really awful application.

I’d love to assume that you put all your PL/SQL procedures and functions into packages. Whether I assume it or not (irrelevant for this post), please do that. Schema-level procedures and functions should be as rare as paintings by Leonardo da Vinci.

See the Feuertips episode on named notation

OK, so you write procedures and functions. Almost all those programs have one or more parameters. And so, finally, we’ve gotten to the point of this post:

How much do I love named notation? Let me count the ways.

=> 1. It makes my code more readable.
=> 2. It makes it easier to modify my code over time.
=> 3. It’s something you can do in PL/SQL that you cannot do in several other popular languages.

Aren’t I clever with the way I worked “=>” into my list?

Because that’s how you get to named notation.

Where can you use named notation?

1. Associating argument values with formal parameters (in PL/SQL and SQL!)
2. Associating an expression with an associative array index value (qualified expression)

Let’s look at an example.

create or replace procedure calculate_annual_carbon_footprint (
   p_miles_driven  in   number,
   p_car_type      in   varchar2,
   p_diet_type     in   varchar2,
   p_miles_flown   in   number,
   p_description   out  varchar2,
   p_co2_out       out  number
) is
   /* Sorry I do not have the expertise to do the calculation! */
   p_co2_out      := 1000;
   p_description  := 'Almost certainly too much if you''re lucky enough to live comfortably.';

With positional notation…

   l_desc  varchar2(1000);
   l_co2   number;
   calculate_annual_carbon_footprint (

The above code compiles, but wow, it is hard to make much sense of it. Some of the values make it pretty clear what is (likely) involved, like “vegetarian” for diet. But you will likely have to go look at the header of the procedure to understand what’s going on.

With named notation…

   l_desc  varchar2(1000);
   l_co2   number;
   calculate_annual_carbon_footprint (
      p_miles_driven  => 10000,
      p_car_type      => 'electric',
      p_diet_type     => 'vegetarian',
      p_miles_flown   => 5000,
      p_description   => l_desc,
      p_co2_out       => l_co2

Now, gee, well, there is no doubt. I know exactly which formal parameter goes with which actual value. Sure, I might need to go check out the procedure definition sometimes to see what it’s doing (or fix a bug). In terms of its use in this block, however, all is clear.

Things to remember about named notation

  • Mix named and positional notation together in a single invocation
  • Change the order of the parameters when invoked (order/position is not important anymore)
  • Once you use named, you cannot go back to positional (you lost the position!)
  • Skip over “internal” parameters with default values (something you absolutely cannot do with positional notation)
  • If all invocations are with named notation, you can change the order of parameters in the definition of the program, and it won’t cause any problems.
  • Works with built-in procedures and functions, too!

The bottom line is that, really, we should always use named notation when invoking subprograms. Some IDEs, like SQL Developer, will help you in this regard, generating the invocation with a click or two. But if you have to do it yourself, it’s usually not all that difficult. Of course, it’s more work the more parameters there are. But the more parameters there are, the more benefit you will get from named notation.

If you can’t bring yourself to do it all the time, though, make very sure you use it for procedures that are rarely called (no one needs to use named notation for dbms_output.put_line or substr!). Anyone working on this code in the future will thank you.

Dive Deeper

For fuller coverage of named notation: https://stevenfeuersteinonplsql.blogspot.com/2019/08/use-named-notation-for-crystal-clear.html

For a LiveSQL script with all the details: https://livesql.oracle.com/apex/livesql/file/content_CJGNZFI9Y0K8HFO6KXJYG8IQB.html

A handy table on Positional, Named, and Mixed Notation for Actual Parameters from Oracle Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-subprograms.html#GUID-A5DA8CF5-1BCC-4ABE-9B68-DB593FF1D2CC

Oracle Dev Gym quizzes on named notation: https://devgym.oracle.com/pls/apex/f?p=10001:SEARCH:945190192771::::P1202_SEARCH:named%20notation


Every other Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
One participant will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us every other Wednesday at 11!
Share this:

Leave reply:

Your email address will not be published. Required fields are marked *