Oracle PL/SQL Best Practices

Name

MOD-04: Use named notation to clarify, self-document, and simplify module calls.

Synopsis

PL/SQL allows you to specify the name of a parameter along with its value in a parameter list by using this format:

parameter name => value

This is called named notation. With named notation, you can change the order in which you supply arguments; you can also skip over IN arguments with default values.

Use named notation whenever you make a call to a program that has any of the following characteristics:

• It has a long, confusing parameter list.

• It’s used infrequently, meaning that there is little familiarity with it or its parameter list.

• It has default values for multiple IN parameters.

• In some cases, it actually requires named notation due to the parameter list design of overloaded programs (as is necessary with the built-in package, DBMS_OBFUSCATION_TOOLKIT).

Example

Here’s a procedure call that relies solely on positional notation (the default in PL/SQL):

IF perform_insert
THEN
   PLGdoir.ins (
      drv,
      c_table,
      NVL (aname, c_global),
      NVL (atype, c_global),
      text_in,
      v_info
   );
END IF;

I wrote that code, but I sure can’t remember which parameter is going to get set to text_in. Here’s another call to the same program:

IF v_tab = c_global
THEN
   PLGdoir.ins (
      driver_in => drv,
      objtype_in => c_table,
      attrname_in => c_global,
      attrtype_in => c_global,
      infotype_in => text_in,
      info_in => v_info
   );

Now I don’t have to wonder; the code tells me exactly what is going on.

Benefits

You will experience a dramatic ...