Overloading and parameters

When you call a procedure or function in PL/SQL you basically have two options for the parameters. By name or by position. If you use the by name version, then every parameter is named. You don’t have to conform to the order of the parameters because Oracle will automatically put the value in the right variable. You can even mix both approaches, start with positional notation and then switch to named notation. Be aware though that you cannot switch back. Once you’re using named notation you cannot go back to positional notation.

So when you create a program (procedure or function) and call this from another program you basically have three options. Consider the following procedure:

   1:  create or replace procedure parameter_usage
   2:  ( parama_in in pls_integer
   3:  , paramb_in in pls_integer
   4:  , paramo_out out pls_integer)
   5:  is
   6:  begin
   7:    paramo_out := parama_in * paramb_in;
   8:  exception
   9:    when others then paramo_out := 0;  
  10:  end parameter_usage;

When this procedure is created I have three options to call it:

   1:  declare
   2:    la pls_integer;
   3:    lb pls_integer;
   4:    loutcome pls_integer;
   5:  begin
   6:    -- define the variables
   7:    la := 6;
   8:    lb := 7;
   9:    -- call the procedure using positional notation
  10:    parameter_usage(la, lb, loutcome);
  11:    dbms_output.put_line(loutcome);
  12:    -- call the procedure using named notation
  13:    parameter_usage(parama_in => la, paramb_in => lb, paramo_out => loutcome);
  14:    dbms_output.put_line(loutcome);
  15:    -- call the procedure using mixed notation
  16:    parameter_usage(la, lb, paramo_out => loutcome);
  17:    dbms_output.put_line(loutcome);  
  18:  end;

As you can see I can use use positional, named or mixed notation. They all lead to the same result. I think writing code that uses named notation increases the readability and understandability of the code. I can read the program that calls the procedure and just by looking at the call I can probably understand what the called program is supposed to do (if the parameter names are descriptive enough).

Note that Oracle does not check the code that is being executed. If you have multiple (non-ambiguous) programs that can be executed then Oracle chooses the one where it needs to do as less implicit type casting as possible. You can of course use this behavior to make your coding efforts easier. For instance when you (like me) create a log package with lots of overloading of the add procedure (different parameter types, different number of parameters etc) you can just call the add procedure without worrying about what parameters to put in here. Oracle will figure out what procedure to call and this procedure will put the values in the right place in the database.

Part of the package specification is like this:

...
  PROCEDURE ADD(
    message_in                     IN      VARCHAR2
  , value_date_in                  IN      DATE );
  PROCEDURE ADD(
    message_in                     IN      VARCHAR2
  , value_number_in                IN      NUMBER );
  PROCEDURE ADD(
    message_in                     IN      VARCHAR2
  , value_varchar2_in              IN      VARCHAR2 );
...
In my code I can just call the add procedure and have it figure out what procedure to use:
   1:  declare
   2:    lnumber pls_integer;
   3:    lstring varchar2(100);
   4:    ldate date;
   5:  begin
   6:    barlog.TurnLogOn;
   7:    lnumber := 42;
   8:    lstring := 'Patrick';
   9:    ldate := to_date('12291972','MMDDYYYY');
  10:    barlog.add( 'number', lnumber);
  11:    barlog.add( 'string', lstring);
  12:    barlog.add( 'date', ldate);
  13:    barlog.show;
  14:    barlog.TurnLogOff;
  15:  end;
  16:  /
Note the lines 10, 11 and 12 are pretty much the same, except for the types of parameter used. The output of this procedure is like this:
Log_id             : 135
Log_user           : PATRICK
Log_date           : 12-FEB-09
Log_message        : number
Log_value_date     : 
Log_value_number   : 42
Log_value_varchar2 : 
Log_id             : 136
Log_user           : PATRICK
Log_date           : 12-FEB-09
Log_message        : string
Log_value_date     : 
Log_value_number   : 
Log_value_varchar2 : Patrick
Log_id             : 137
Log_user           : PATRICK
Log_date           : 12-FEB-09
Log_message        : date
Log_value_date     : 29-DEC-72
Log_value_number   : 
Log_value_varchar2 : 
 

Notice how Oracle chose the right procedure based on the parameter type, not the position of this parameter. Of course some coding has to be done in the log package to write the data out to the right columns in the table, but this logic is hidden in the package body. If I want Oracle choose exactly a single program in a package, I can help its choice by putting in the right names for the different parameters. I could for instance have the log_value_varchar2 field with a number, but the I would have to explicitly tell the engine to choose that program.

This code

   1:  declare
   2:    lnumber pls_integer;
   3:  begin
   4:    barlog.TurnLogOn;
   5:    lnumber := 42;
   6:    barlog.add( 'number in number', lnumber);
   7:    barlog.show;
   8:    barlog.TurnLogOff;
   9:  end;
  10:  /

would result in this:

Log_id             : 138
Log_user           : PATRICK
Log_date           : 17-FEB-09
Log_message        : number in number
Log_value_date     : 
Log_value_number   : 42
Log_value_varchar2 : 

Whereas this code

   1:  declare
   2:    lnumber pls_integer;
   3:  begin
   4:    barlog.TurnLogOn;
   5:    lnumber := 42;
   6:    barlog.add( message_in => 'number in varchar2', value_varchar2_in => lnumber);
   7:    barlog.show;
   8:    barlog.TurnLogOff;
   9:  end;
  10:  /

would result in this:

Log_id             : 139
Log_user           : PATRICK
Log_date           : 17-FEB-09
Log_message        : number in varchar2
Log_value_date     : 
Log_value_number   : 
Log_value_varchar2 : 42

Notice that Oracle must be able to perform an implicit conversion here. If I were to try to send a character value into a numeric parameter and the conversion is not possible, then I would be presented with an exception.

In some cases, it is better to use named parameters, improve readability of your code. In some cases it is better to use positional parameters. Have Oracle do the heavy lifting, make the right choice for you.

4 thoughts on “Overloading and parameters

  1. Maybe I’m missing something here, but I’m not sure I agree with this.
    While it is certainly fine to use positional notation, I’m not convinced that it is ever BETTER to use positional notation. Ultimately, I should always know what procedure is being called (the varchar version, the boolean version, etc) and so I can always know how to implement the named parameters.

    I suppose I might call a procedure passing in a varchar2 that can be converted into a number, but it is still going to take the version that is expecting a varchar2, so the string itself doesn’t matter.

    Using positional notation limits the flexibility of your code and gains you nothing.


  2. I think my point is that you at least have a choice to do your own ‘heavy lifting’ or have Oracle do it for you. Do you also name your parameters when you use for instance dbms_output.put_line? I know I’m not…


  3. Oh sure. My point is just that the ‘heavy lifting’ is minimal and that really means that the benefits of being lazy (and I am ALL about being lazy) are also minimal and are much more likely to cause problems later on.


  4. … but DBMS_OUTPUT.PUT_LINE is not overloaded:
    PROCEDURE PUT_LINE
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    A VARCHAR2 IN

    🙂


Leave a Reply to Joel Cancel reply

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