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:

   <source lang="sql">

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.</source>

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

   <source lang="sql">

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></source>

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.

   <source lang="sql">

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.</source>

Number of parameters

   <source lang="sql">

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></source>

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.

   <source lang="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) 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.</source>