PLS-00382: expression is of wrong type. PL/SQL: Statement ignored

Question

I tried executing a simple package with function overloading. Below is the package code.

--package specification:
create or replace package over_load as
    FUNCTION print_it(v_date date) return date;
    FUNCTION print_it(v_name varchar2) return number;
end over_load;

--package body:
create or replace package body over_load as
    FUNCTION print_it(v_date date) return date is --function accepting and returning date
        begin
        dbms_output.put_line('the date is ' || v_date);
        return v_date;
    end print_it;

    FUNCTION print_it(v_name varchar2) return number is /*function accepting string and returning number*/
        v_eno employees.employee_id%type;
        begin
        select employee_id into v_eno from employees where first_name = v_name;
        return v_eno;
        end print_it;
    end over_load;

I tried executing the first function in the package using the below anonymous block.

declare
sample_date date;
begin
sample_date := over_load.print_it('14-07-2017');
dbms_output.put_line(sample_date);
end;

I tried passing date as the argument to the first function, but it throws the wrong argument type error. Any idea on why?

Answer 1

If the procedure (or a function) expects DATE datatype, then don't pass string to it. Because, '14-07-2017' is a string.

SQL> set serveroutput on
SQL>
SQL> declare
  2    sample_date date;
  3  begin
  4    --sample_date := over_load.print_it('14-07-2017');
  5    sample_date := over_load.print_it(date '2017-07-14');
  6    dbms_output.put_line(sample_date);
  7  end;
  8  /
the date is 14.07.17
14.07.17

PL/SQL procedure successfully completed.

SQL>

In line #5, I passed a date literal. It could have also been to_date('14-07-2017', 'dd-mm-yyyy').

Oracle - if it can - implicitly converts what you pass to datatype it expects, but it doesn't always succeed; that depends on NLS settings.

To be sure that it'll ALWAYS work, take control over it and use appropriate datatypes.