Oracle PL/SQL does not recognize a DATE input?

Question

I have package:

create or replace PACKAGE overload_pkg IS
  PROCEDURE what_am_i (p_x IN VARCHAR2);
  PROCEDURE what_am_i (p_x IN NUMBER);
  PROCEDURE what_am_i (p_x IN DATE);
END overload_pkg;

and body:

create or replace PACKAGE BODY overload_pkg IS
  PROCEDURE what_am_i (p_x IN VARCHAR2) IS
  BEGIN 
    pri('Here I am, a VARCHAR2');
  END what_am_i;
  PROCEDURE what_am_i (p_x IN NUMBER) IS
  BEGIN 
    pri('Here I am, a NUMBER');
  END what_am_i;
  PROCEDURE what_am_i (p_x IN DATE) IS
    BEGIN 
    pri('Here I am, a DATE');
  END what_am_i;
END overload_pkg;

Yet, when I call package PROCEDUREs:

BEGIN 
  overload_pkg.what_am_i('varchar2');
  overload_pkg.what_am_i(1234);
  overload_pkg.what_am_i('2018-04-06');
END;

, or any other date format I could think of, Oracle thinks it is either a VARCHAR or a NUMBER. Output:

Here I am, a VARCHAR2
Here I am, a NUMBER
Here I am, a VARCHAR2

Statement processed.

The only thing that worked was...

BEGIN 
  overload_pkg.what_am_i('varchar2');
  overload_pkg.what_am_i(1234);
  overload_pkg.what_am_i(to_DATE('08-APR-2018', 'DD-MON-YYYY'));
END;

But I thought Oracle recognized dates, given that it does implicit conversions. I am confused.

Thanks in advance.

`overload_pkg.what_am_i('2018-04-06');` Oracle is working correctly here. You are passing a string - a series of characters bounded by single-quotes. The fact that the value inside the string could be successfully cast to a date does not change that. What would you expect to happen if you change your second call to `overload_pkg.what_am_i('1234');`? — APC, Apr 08 '18 at 13:27
Oracle will convert a string to a date **only if needed** - that is, if a date is REQUIRED. In your case, a string works OK - Oracle doesn't need to convert it to string to "make things work." Perhaps if you had NOT defined a version that accepts a string, it would try to convert to date - but even then, that would fail because Oracle does **not** "recognize dates" (regardless of what you thought); it only "recognizes dates" that match the session `NLS_DATE_FORMAT` parameter. If your `NLS_DATE_FORMAT` is `'dd-MON-yyyy'`, the input `'2018-04-06'` will NOT be recognized as a date. — , Apr 08 '18 at 18:39

score 2 · Answer 1 · answered Apr 08 '18 at 13:04

That's expected; when you ran

overload_pkg.what_am_i('2018-04-06');

you passed a string to the procedure; see the single quotes and something in between? Well, yes - that's a string, VARCHAR2.

Implicit conversion doesn't count here; it might, if you used that string (that looks like a DATE) in some query. Oracle didn't have to convert anything here; you sent a string, it was detected, and appropriate procedure was executed.

If you want to pass a DATE, then do so - either by using the DATE literal, which always comes in DATE 'YYYY-MM-DD' format, or apply the TO_DATE function with the appropriate format mask.

Thanks, @Littlefoot. That helps a lot – dmark Apr 08 '18 at 13:43 — dmark, Apr 08 '18 at 13:43

Lukasz Szozda · Answer 2 · 2018-04-08T13:17:16.707

1

You could use DATE literal:

BEGIN
   overload_pkg.what_am_i(DATE '2018-04-06');
END

From How Subprogram Calls Are Resolved:

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. They must match in number, order, and datatype (unless some formal parameters were assigned default values). If no match is found or if multiple matches are found, the compiler generates a semantic error.

edited Apr 08 '18 at 13:17

answered Apr 08 '18 at 12:58

Lukasz Szozda

162,964
23
234
275

1

Awesome! Thanks! I am learning here. I had used every date format I could think of but didn't know that "DATE literal" actually included the word "DATE". – dmark Apr 08 '18 at 13:40

Oracle PL/SQL does not recognize a DATE input?

2 Answers2