PL/SQL Tip of the Month

From the "PL/SQL Pipeline" - a free internet-based community for Oracle Developers. The PL/SQL Pipeline is hosted by renowned PL/SQL author Steven Feuerstein, and sponsored by RevealNet. Stop by anytime by pointing your web browser to http://www.revealnet.com/plsql-pipeline/index.htm

January's Tip of the Month
The Power of Positional Notation

Did you know that there are two different ways to pass arguments to procedures and functions? They are called positional notation and named notation. Most developers only know about the first. That's too bad, because named notation can come in very handy. Consider this procedure:

PROCEDURE myproc (

   parm1 DATE,
   parm2 NUMBER := 0,
   parm3 BOOLEAN := FALSE,
   parm4 VARCHAR2
   );

Here is the positional notation way to call this procedure:

BEGIN
   myproc (SYSDATE, 15, FALSE, 'abc');

In other words, the runtime engine lines up the first argument (SYSDATE) with the first parameter (parm1), the second argument (15) with the second parameter (parm2), and so on.

Suppose, however, that you only want to pass a value for parm1 and parm2, relying on the default values for the middle two parameters? You can't do this:

BEGIN
   myproc (SYSDATE, ,, 'abc'); -- Invalid syntax in PL/SQL!

You can do this, but then you have to know what the default values are:

BEGIN
   myproc (SYSDATE, 0, FALSE, 'abc');
-- It's not easy to find the default values!

Or you can use named notation as shown below:

BEGIN
   myproc (parm1 => SYSDATE, parm4 => 'abc');

The two-character symbol "=>" says, in so many PL/SQL words: "associate this parameter with this value". With named notation you can "skip over" parameters with default values. You can also change the order in which you supply values. Finally, named notation is often useful for "self-documenting" which values go with which arguments. This is especially useful when calling programs with long parameter lists and when calling programs in the built-in packages.

You can find more information about named notation in:

•The RevealNet PL/SQL Knowledge Base. Just "fast track" on "named notation". •My Oracle PL/SQL Programming, 2nd Edition (www.ora.com) on pages 502-504.

Happy New Year!

Cam White
RevealNet, Inc. Received on Tue Jan 06 1998 - 00:00:00 CET