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 begin /* 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.'; end; /
With positional notation…
declare l_desc varchar2(1000); l_co2 number; begin calculate_annual_carbon_footprint ( 10000, 'electric', 'vegetarian', 5000, l_desc, l_co2 ); end; /
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…
declare l_desc varchar2(1000); l_co2 number; begin 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 ); end; /
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.
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