Overloading with numeric types

View All Scripts Login to Run Script

Script Name Overloading with numeric types
Description From the documentation: "You can overload subprograms if their formal parameters differ only in numeric data type. PL/SQL looks for matching numeric parameters in this order: PLS_INTEGER (or BINARY_INTEGER, an identical data type); NUMBER (including integer); BINARY_FLOAT; BINARY_DOUBLE. You cannot successfully overload VARCHAR2 and CHAR, but you can overload for different numeric types.
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday July 28, 2021

Statement 1

This overloading fails to compile, because the PL/SQL engine cannot determine if an string argument should be treated as varchar2 or char.

varchar2 vs char not different enough

declare  
   procedure proc1 (  
      n in varchar2  
   ) is  
   begin  
      DBMS_OUTPUT.PUT_LINE('varchar2 version');  
   end;  
  
   procedure proc1 (  
      n in char  
   ) is  
   begin  
      DBMS_OUTPUT.PUT_LINE('char version');  
   end;  
begin  
   proc1(1.1);  
   proc1(1);  
end;

ORA-06550: line 16, column 4:
PLS-00307: too many declarations of 'PROC1' match this call

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 2

I can successfully overload between pls_integer and number (which includes integer). pls_integer is a PL/SQL-only datatype with a constrained range of values. If a literal falls within that range or a pls_integer variable is specified, then the pls_integer overloading is used. Notice that when I pass an expression like 2**31-1, it treats it as a number, not a pls_integer, even if the value is within the range.

Automatic determination of number vs pls_integer

declare  
   plsi pls_integer := 2**30;  
     
   procedure proc1 (  
      n in pls_integer  
   ) is  
   begin  
      DBMS_OUTPUT.PUT_LINE('pls_integer version');  
   end;  
  
   procedure proc1 (  
      n in number  
   ) is  
   begin  
      DBMS_OUTPUT.PUT_LINE('number version');  
   end;  
begin  
   proc1(1.1);  
   proc1(1);  
   proc1(plsi);  
   proc1(2147483647); -- 2**31 - 1  
   proc1(2147483648); -- 2**31   
   proc1(2**1);   
end;

Statement processed.
number version
pls_integer version
pls_integer version
pls_integer version
number version
number version

Statement 3

Package for next demonstration

create or replace package ol is  
   function run_stuff (  
      p_n  in  pls_integer  
   ) return varchar2;  
  
   function run_stuff (  
      p_n  in  integer  
   ) return varchar2;  
end;

Package created.

Statement 4

Package for next demonstration

create or replace package body ol is  
   function run_stuff (  
      p_n  in  pls_integer  
   ) return varchar2 is  
   begin  
      return 'pls_integer-' || p_n;  
   end;  
  
   function run_stuff (  
      p_n  in  integer  
   ) return varchar2 is  
   begin  
      return 'integer-' || p_n;  
   end;  
end;

Package Body created.

Statement 5

This is interesting! When I execute my overloaded procedure via dynamic SQL, passing the same value, it uses a different overloading. I assume this is happening because pls_integer is a PL/SQL-only type. But notice that when I concatenate the value, it becomes a literal, and then it is recognized by the PL/SQL engine as pls_integer.

Dynamic SQL chooses the SQL type

declare  
   l_value1  pls_integer := 1;  
   l_value2  pls_integer := 1;  
begin  
   execute immediate 'BEGIN   
      DBMS_OUTPUT.PUT_LINE (ol.run_stuff (:value1));   
    END;'  
      using l_value1;  
 
   execute immediate 'BEGIN   
      DBMS_OUTPUT.PUT_LINE (ol.run_stuff ('|| l_value1 || '));   
    END;';  
 
   dbms_output.put_line(ol.run_stuff(l_value1));  
end;

Statement processed.
integer-1
pls_integer-1
pls_integer-1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database