Explain Plan with Date bind variables

[johnsok]

For PLSD 7.0.2.1076, when pasting in a SQL string (from Sessions), the SYSDATE becomes a bind variable :B2, however when you try and run an explain plan against this SQL you receive an 'ORA-00932 inconsistent data types: expected DATE got NUMBER' error. When I manually change the :B2 bind variables to SYSDATE, the explain plan functions properly. Is there anyway to somehow detect when a DATE is being utilized with any kind of expression, I guess substituting SYSDATE would work (that is all I did). Well, I guess the more I think about this, who knows what silliness is contained within :B2, you would be second-guessing the real code. Just thought I'd throw it out there in case there is some other feature (button?) for explain plan that magically makes the date comparisons work without manual intervention.

SELECT NULL FROM
TABLE_A a
WHERE
(TRUNC(a.SCHE_DATE) >= :B2 - :B5 ))
OR
(TRUNC(a.SCHE_DATE) BETWEEN :B2 AND B2 + :B1 ))

 

[johnsok]

Had one extra right parenthesis (had to pare down a huge select statement to bare bones). Here is the corrected syntax:

SELECT NULL FROM
TABLE_A a
WHERE
(TRUNC(a.SCHE_DATE) >= :B2 - :B5 )
OR
(TRUNC(a.SCHE_DATE) BETWEEN :B2 AND B2 + :B1 ))