CODEWITHSUNDEEP
oracle-database
Sam
Sam

Reputation: 7678

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

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 do I test for a valid date?

Upvotes: 2

Views: 14647

Answers (2)

Tom Hubbard
Tom Hubbard

Reputation: 16139

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.

Upvotes: 5

Pop
Pop

Reputation: 4022

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;
/

Upvotes: 2

Related Questions