Testing for a valid date in a oracle procedure's parameter

Question

Hi,

Oracle 8 here.

When passing a date to a procedure, I can pass a '' and nothing is thrown.

Testing for MYDATE := '' doesn't eval to true. Nor does LENGTH < 1. DBMS_OUTPUT shows nothing coming through the parameter.

Trying to pass '01-30-2009' (instead of 30-JAN-2009) throws an invalid date error.

How is passing a zero length string valid?

How to I test for a valid date?

Thanks!

Answer 1

In later versions of Oracle the empty string is considered the same as NULL. That is probably what you are running into.

You may be able to set the parameter to not null and then it ought to error out. (As Jeffery Kemp noted in the comments, you can NOT use not null for a parameter)

As far as the invalid date error Oracle will implicitly cast a string to a date if it is in the format dd-mmm-yyyy. Otherwise you will have to run it through to_date with the proper mask.

I am not familiar with Oracle 8 so I'm not sure what is new or not. Hopefully this helps.

Answer 2

Personally, I think a date is a date and a string is a string. I wish there was a way to disable implicit conversion. But if you control the program that's calling the procedure, you can try:

call my_proc(to_date('01-30-2009','MM-DD-YYYY'));

instead of:

call my_proc('01-30-2009');

Otherwise, make your procedure receive a string and check the format inside the procedure:

create procedure my_proc(p_date_str in varchar2) is
  v_dt date;

begin
  if length(v_dt) != 10 then
    raise_application_error(-20000,'Wrong date format',true);
  end if;
  v_dt := to_date(p_date_str,'MM-DD-YYYY');
  ... now use v_dt as a date ...
end;
/