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

From: Juha Laiho <>
Date: Wed, 29 Oct 2008 17:02:03 GMT
Message-ID: <gea4lk$d54$1@ichaos2.ichaos-int> 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, I did encounter it on (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

  update mytable

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

use the below form:

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

     set status='silent'
   where lastping < time_limit;

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)
Received on Wed Oct 29 2008 - 12:02:03 CDT

Original text of this message