Passing date to Stored Procedure in Oracle SQL error : ORA-01858

I know this question has been answered previously, and I have tried out the solution given in them without success.

I have a stored procedure as below(I have removed non-essential parts) :

CREATE OR REPLACE PROCEDURE get_days(dt date)
IS
  given_date DATE := TO_DATE('dt', 'DD-MON-YYYY');
BEGIN
  get_day := RTRIM(TO_CHAR(dt1, 'DAY'));
  DBMS_OUTPUT.PUT_LINE ('The day of the given date is '||get_day||);
  DBMS_OUTPUT.PUT_LINE ('Execution  done successfully.');
END get_days;

I am having trouble with passing date to the procedure get_days. I have tried in the following ways :

BEGIN
  --Below I have listed all ways I have tried 
  get_days('12/12/12');
  get_days('12-12-12');
  get_days(date '2012-12-12');  
  get_days(TO_DATE( '01/01/2018', 'MM/DD/YYYY' ));
END;

I don’t know what is wrong but I am getting this error – ORA-01858: a non-numeric character was found where a numeric was expected. I have searched for similar errors online but none helped. I am using Oracle Database 11g Express edition. There is probably some minor error in my code but I am not getting what it is.

Any help is highly appreciated !

Answer

The immediate cause of the error is that you have

  given_date DATE := TO_DATE('dt', 'DD-MON-YYYY');

instead of

  given_date DATE := TO_DATE(dt, 'DD-MON-YYYY');

In your version you are trying to convert the string literal 'dt' to a date; that literal has no relationship to your dt argument.

However, this is still wrong, as dt is declared as a date datatype already. If you do that then you are really doing:

  given_date DATE := TO_DATE(TO_CHAR(dt), 'DD-MON-YYYY');

which will use your current session NLS_DATE_FORMAT to convert the date to a string; if that isn’t ‘DD-MON-YYYY’ too (or something close enough that Oracle will fudge it) then you will still get an error or, worse, incorrect conversion and invalid results that you might not notice.

So remove that redundant and dangerous conversion:

CREATE OR REPLACE PROCEDURE get_days(dt date)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('The day of the given date is ' || TO_CHAR(dt, 'FMDAY') || '.');
  DBMS_OUTPUT.PUT_LINE ('Execution  done successfully.');
END get_days;
/

You can of course assign the day name to a string variable if you want. I’ve used the FM format modifier to stop it right-padding the day name, so you don’t need to trim it – the full-stop is just there to demonstrate that works. If you want the day name in mixed case you can use 'FMDay' instead of 'FMDAY'.

You should always pass in a date, not a string:

set serveroutput on;

BEGIN
  get_days(date '2012-12-12');  
  get_days(TO_DATE( '01/01/2018', 'MM/DD/YYYY' ));
END;
/

The day of the given date is WEDNESDAY.
Execution  done successfully.
The day of the given date is MONDAY.
Execution  done successfully.


PL/SQL procedure successfully completed.

dt might be passed in any format. I am just converting it into DD-MM-YYYY format and storing into given_date

A date doesn’t have a format; it has an internal representation which you very rarely need to know or care about. When you query a date value without explicitly converting it to a string, your client converts that to a human-readable form, usually using NLS_DATE_FORMAT to decide what format to display it in. So dt does not have a format, and even if the caller makes the call with a string (as in your first two examples) those will be implicit converted to a date – assuming that’s possible – before the call; the procedure will never see those strings, it will only see the converted date value.