CODEWITHSUNDEEP
oracle-database
Alistair Bayley
Alistair Bayley

Reputation: 341

sysdate and current_date have different types?

The oracle docs for sysdate and current_date claim they both return DATEs:

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 output:

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 runs a risk of altering 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

Upvotes: 8

Views: 1313

Answers (1)

Nilesh Deshpande
Nilesh Deshpande

Reputation: 120

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

2) If you still need CURRENT_DATE, following is the solution. Store the value of current_date into variable and it will resolve your problem. Let me if this answers 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.

Upvotes: 1

Related Questions