Two Quick and Simple Tips That Will Help You Write Better PL/SQL

When invoking a PL/SQL procedure or function, you can specify the value of its parameters using either positional, named, or mixed notation. Let’s review what each notation means and then demonstrate the best way to add new parameters to an existing subprogram without breaking existing code.

Consider this simple package as an example:

CREATE OR REPLACE PACKAGE ed_test_pkg
AS
   PROCEDURE proc1 (
      param1_in    IN       NUMBER,
      param2_in    IN       NUMBER,
      param1_out   OUT      NUMBER
   );
END ed_test_pkg;
/

CREATE OR REPLACE PACKAGE BODY ed_test_pkg
AS
   PROCEDURE proc1 (
      param1_in    IN       NUMBER,
      param2_in    IN       NUMBER,
      param1_out   OUT      NUMBER
   )
   AS
   BEGIN
      param1_out := param1_in + param2_in;
   END proc1;
END ed_test_pkg;
/

Positional Notation:

When you invoke a procedure using positional notation, you specify the parameters in the same order as the procedure declares them. For example:

DECLARE
   l_param1_out   NUMBER;
BEGIN
   ed_test_pkg.proc1 (1, 2, l_param1_out);
   DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;

Named Notation:

When you invoke a procedure using named notation, you specify the name and value of each parameter, using the association operator =>. The order of parameters is irrelevant. For example:

DECLARE
   l_param1_out   NUMBER;
BEGIN
   ed_test_pkg.proc1 (param2_in       => 2,
                      param1_out      => l_param1_out,
                      param1_in       => 1
                     );
   DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;

Mixed Notation:

When you invoke a procedure using mixed notation, you start with positional notation, then use named notation for the remaining parameters. For example:

DECLARE
   l_param1_out   NUMBER;
BEGIN
   ed_test_pkg.proc1 (1, param2_in => 2, param1_out => l_param1_out);
   DBMS_OUTPUT.put_line ('param1_out = ' || l_param1_out);
END;

Which one to use?

After this quick notations refresher, the question is which one to choose as your preferred style when invoking PL/SQL programs? For me, I prefer, and almost always use, the named notation, and you should too. OK, I admit there is more typing involved in named notation, but it is easier to read and maintain than the other two. Moreover, if the program’s parameter list changes, you avoid changing the code that invokes the program using named notation.

Adding a new parameter

Speaking of changing the parameter list, let’s say you have to add a new param3_in IN parameter to the procedure ed_test_pkg.proc1. Which one of the following two approaches do you use?

Approach 1:

PROCEDURE proc1 (
   param1_in    IN       NUMBER,
   param2_in    IN       NUMBER,
   param3_in    IN       NUMBER DEFAULT 1, 
   param1_out   OUT      NUMBER
);

Approach 2:

PROCEDURE proc1 (
   param1_in    IN       NUMBER,
   param2_in    IN       NUMBER,
   param1_out   OUT      NUMBER,
   param3_in    IN       NUMBER DEFAULT 1
);

As you know, adding DEFAULT to the IN parameter means that you do not have to pass in a value to this parameter, and if you don’t provide a value, the default value is used.

Now, you may be tempted to keep all the IN parameters together, however, the second approach is better. Adding the parameter to the end of the parameter list is safer. Let me show you why.

Let’s say you have a program that calls ed_test_pkg.proc1 using the positional notation. When you add a new IN parameter using the first approach, and if you do not make any further modifications to the code, you’ll get an error:

SQL> DECLARE
  2     l_param1_out   NUMBER;
  3  BEGIN
  4     ed_test_pkg.proc1 (1, 2, l_param1_out);
  5     DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
  6  END;
  7  /
   ed_test_pkg.proc1 (1, 2, l_param1_out);
   *
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored

But, you will not get this error if you use approach 2 or if you use the named notation when calling ed_test_pkg.proc1.

Conclusion

So, in general, as a best practice, always use named notation when invoking PL/SQL procedures and functions. To avoid breaking existing code, add new parameters with DEFAULT values to the end of the parameter list of existing procedures or functions.

Source and Resources:

• Adding IN parameter
• Using Positional, Named, or Mixed Notation for PL/SQL Subprogram Parameters
• 11g New Features: Efficient PL/SQL Coding

Filed in Oracle, Tips with 8 Comments | Tags: parameter, pl/sql

---