Sysdate and current_date have different types?

Question

The oracle docs for sysdate and current_date claim both return DATE:

• sysdate
• current_date

This test though:

alter session set plsql_warnings = 'ENABLE:ALL'; create table test(x date); create or replace procedure test1 authid definer is cursor s is select x from test where current_date > x; begin for x in s loop null; end loop; end; / show errors drop table test; drop procedure test1; 

produces this conclusion:

 Errors for PROCEDURE TEST1: LINE/COL ERROR 3/42 PLW-07204: conversion away from column type may result in sub-optimal query plan 

Using sysdate does not give the same warning. I suspect that substituting current_date for sysdate in queries results in a risk of changing the query plan, especially if the date columns are indexed.

edit:

 select dump(current_date) from dual; select dump(sysdate) from dual; 

gives:

 DUMP(CURRENT_DATE) Typ=13 Len=8: 223,7,7,9,11,23,55,0 DUMP(SYSDATE) Typ=13 Len=8: 223,7,7,9,11,23,55,0 

Answer 1

1) CURRENT_DATE returns the current date in the time zone of the session. Do you really need current_date? If not, stick with sysdate. This will work with your procedure.

2) If you still need CURRENT_DATE, then this is the solution. Store the value of current_date in a variable and it will solve your problem. Let me answer your question.

 drop table test; create table test(x date); create or replace procedure test1 authid definer is dateVar date; cursor s is select x from test where dateVar > x; begin dateVar:=current_date; for x in s loop null; end loop; end; / SQL> show errors No errors.