Re: comparing a DATE column with "one minute ago", type warning?

mh_at_pixar.com said:
>How should I be comparing a DATE column with "one minute ago"?
...
>But in my PL/SQL, I get a warning on this code, where
>lastping is a DATE column:
>
> update mytable
> set status='silent'
> where lastping < (sysdate - interval '1' minute);
> ^ ^
> col 28 col 36
>
>Warning(16,28): PLW-07202: bind type would result in conversion
> away from column type
>Warning(16,36): PLW-07202: bind type would result in conversion
> away from column type

I encountered this same today, and found out that this is a known Oracle bug; in Oracle MetaLink bug database this is identified by bug number 5895688. Based on Oracle documentation (thanks for everyone in this thread for providing the proper starting points in the docs!), I would claim that your syntax is correct. The bug was reported to exist on 10.2.0.1, I did encounter it on 10.2.0.3 (Linux x86-64), and based on the comments on MetaLink, it was not yet resolved.

I also did find a workaround for this. You can avoid the warning by creating a variable of the correct type to hold the intermediate result, and the use that variable instead of the expression in your SQL statement. So, instead of

begin
  update mytable

     set status='silent'
   where lastping < (sysdate - interval '1' minute); end;

use the below form:

declare
  time_limit date;
begin
  time_limit := sysdate - interval '1' minute;   update mytable

     set status='silent'
   where lastping < time_limit;
end;

If you end up using the above workaround, it might be good to place a comment into your code telling that the extra variable is used solely to circumvent Oracle bug 5895688 (as a reminder for a future maintainer that the code may be cleaned up, if the Oracle bug has been fixed).

-- 
Wolf  a.k.a.  Juha Laiho     Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
         PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)