Sunday, October 16, 2016

When Compiler Catches Overloading Errors

When Compiler Catches Overloading Errors

I have a procedure INITIALIZE which accepts CHAR as IN parameter , another procedure INITIALIZE accepts VARCHAR2 as IN parameter, Now I'm calling INITIALIZE procedure with IN parameter value as 'AB'. Which procedure will execute?

In fact , the compiler throws PLS-00307: too many declarations of 'INITIALIZE' match this call. See the below demonstration .


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as HR@ORCL
SQL> 
SQL> DECLARE
  2    PROCEDURE initialize ( in_val CHAR) IS
  3    BEGIN
  4    dbms_output.put_line('CHAR data type '||in_val);
  5    END initialize;
  6  
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 11  
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  
 16  /
DECLARE
  PROCEDURE initialize ( in_val CHAR) IS
  BEGIN
  dbms_output.put_line('CHAR data type '||in_val);
  END initialize;

  PROCEDURE initialize ( in_val VARCHAR2) IS
  BEGIN
  dbms_output.put_line('VARCHAR2 data type '||in_val);
  END initialize;

BEGIN
  initialize('AB');
END;
ORA-06550: line 13, column 3:
PLS-00307: too many declarations of 'INITIALIZE' match this call
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored


The above procedure program throws error PLS-00307, because :

"The PL/SQL compiler catches overloading errors as soon as it can determine that it will be unable to tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overloading error when you try to compile the subprograms themselves (if they are local) or when you try to compile the package specification that declares them (if they are packaged); otherwise, it catches the error when you try to compile an ambiguous invocation of a subprogram."

Let's correct the above program by changing the synonymous procedures to different procedures, i m changing the CHAR datatype to NUMBER in the first procedure. 

The anonymous block compiles successfully and executes.


SQL> DECLARE
  2    PROCEDURE initialize ( in_val NUMBER) IS
  3    BEGIN
  4    dbms_output.put_line('NUMBER data type '||in_val);
  5    END initialize;
  6  
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 11  
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  /
VARCHAR2 data type AB
PL/SQL procedure successfully completed