The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

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:

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:

...

PROCEDUREADD(

message_in IN VARCHAR2

, value_date_in INDATE );

PROCEDUREADD(

message_in IN VARCHAR2

, value_number_in IN NUMBER );

PROCEDUREADD(

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.

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.

Post navigation

4 thoughts on “Overloading and parameters”

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.

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…

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.