Oracle PL/SQL Tutorial/Function Procedure Packages/Overloading

Datatype family of parameters

Datatype families are groups of similar datatypes.

For example, CHAR and VARCHAR2 are used to describe exactly the same kind of textual data, so they belong to the same family.

You can overload only between different families.

The following code is an example of declaring a different datatype family:

SQL> declare
  2    function getArea(i_rad NUMBER, i_prec NUMBER) return NUMBER is
  3        v_pi NUMBER:=3.14;
  4    begin
  5       return trunc(v_pi * (i_rad ** 2),i_prec);
  6    end;
  7    function getArea(i_rad NUMBER, i_ignore_yn VARCHAR2) return NUMBER is
  8        v_pi NUMBER:=3.14;
  9    begin
 10       if i_ignore_yn="Y" and i_rad < 5 then
 11         return 0;
 12       else
 13         return v_pi * (i_rad ** 2);
 14       end if;
 15    end;
 16  begin
 17    DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3,1));
 18    DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3,"N"));
 19  end;
 20  /
Area (R=3):28.2
Area (R=3):28.26
PL/SQL procedure successfully completed.

More than one match was found so Oracle raised a special error.

SQL>
SQL> declare
  2      function getArea(i_rad NUMBER) return NUMBER
  3      is
  4          v_pi NUMBER:=3.14;
  5      begin
  6         return v_pi * (i_rad ** 2);
  7      end;
  8      function getArea(i_length NUMBER, i_width NUMBER:=3) return NUMBER
  9      is
 10      begin
 11         return i_length * i_width;
 12      end;
 13  begin
 14     DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
 15  end;
 16  /
   DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
                                       *
ERROR at line 14:
ORA-06550: line 14, column 40:
PLS-00307: too many declarations of "GETAREA" match this call
ORA-06550: line 14, column 4:
PL/SQL: Statement ignored

SQL>

Names of parameters

You can overload program units by using different names of parameters as long as you use named notation when you call the program units.

SQL>
SQL> declare
  2      function getArea(i_rad NUMBER, i_prec NUMBER)
  3         return NUMBER
  4      is
  5          v_pi NUMBER:=3.14;
  6      begin
  7         return trunc(v_pi * (i_rad ** 2),i_prec);
  8      end;
  9      function getArea(i_length NUMBER, i_width NUMBER)
 10         return NUMBER
 11      is
 12      begin
 13         return i_length * i_width;
 14      end;
 15  begin
 16     DBMS_OUTPUT.put_line("Area (R=3): "||getArea(i_rad=>3,i_prec=>1));
 17     DBMS_OUTPUT.put_line("Area (2x3): "||getArea(i_length=>2,i_width=>3));
 18  end;
 19  /
Area (R=3): 28.2
Area (2x3): 6
PL/SQL procedure successfully completed.

Number of parameters

SQL>
SQL> declare
  2      function getArea(i_rad NUMBER)
  3         return NUMBER
  4      is
  5          v_pi NUMBER:=3.14;
  6      begin
  7         return v_pi * (i_rad ** 2);
  8      end;
  9      function getArea(i_length NUMBER, i_width NUMBER)
 10         return NUMBER
 11      is
 12      begin
 13         return i_length * i_width;
 14      end;
 15  begin
 16     DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
 17     DBMS_OUTPUT.put_line("Area (2x3):"||getArea(2,3));
 18  end;
 19  /
Area (R=3):28.26
Area (2x3):6
PL/SQL procedure successfully completed.
SQL>
SQL>

Overloading calls

You can declare local or packaged stored procedures with exactly the same name, as long as their parameters are different by at least one of these factors:

1. the number of parameters,
2. names of parameters,
3. order of parameters, or
4. the datatype family of the parameters.

27. 19. Overloading 27. 19. 1. Overloading calls 27. 19. 2. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Numberofparameters.htm">Number of parameters</a> 27. 19. 3. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Namesofparameters.htm">Names of parameters</a> 27. 19. 4. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Datatypefamilyofparameters.htm">Datatype family of parameters</a> 27. 19. 5. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Therearesomerestrictionsonoverloading.htm">There are some restrictions on overloading:</a> 27. 19. 6. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/MorethanonematchwasfoundsoOracleraisedaspecialerror.htm">More than one match was found so Oracle raised a special error.</a>

There are some restrictions on overloading:

You can"t overload standalone procedures or functions.

The second definition simply overwrites the first one.

You can"t overload functions that differ only by the datatype of the return value.

If you need to implement this requirement, use overloaded procedures with OUT parameters.

SQL> declare
  2     function getArea(i_rad NUMBER) return NUMBER
  3      is
  4          v_pi NUMBER:=3.14;
  5      begin
  6         return v_pi * (i_rad ** 2);
  7      end;
  8      function getArea (i_length NUMBER, i_width NUMBER) return NUMBER
  9      is
 10      begin
 11         return i_length * i_width;
 12      end;
 13  begin
 14     DBMS_OUTPUT.put_line("Area (R=3): " ||getArea(3));
 15     DBMS_OUTPUT.put_line("Area (R=3): " ||getArea("3"));
 16  end;
 17  /
Area (R=3): 28.26
Area (R=3): 28.26
PL/SQL procedure successfully completed.