error when using INTERVAL in pl/sql

[mstanton]

I am performing date / time manipulation in pl/sql. The following SQL runs fine.

select sysdate - interval '60' minute from dual;
the result = 6/24/2003 8:39:34 AM

But when I put it in pl/sql I get the following error:

create or replace function time_diff return date is
v_result date;
begin
select sysdate - interval '60' minute into v_result from dual;
return(v_result);
end time_diff;

Compilation errors for FUNCTION XOSOR.TIME_DIFF

Error: PLS-00201: identifier 'NUMTODSINTERVAL' must be declared
Line: 4
Text: select sysdate - interval '60' minute into v_result from dual;

Error: PL/SQL: SQL Statement ignored
Line: 4
Text: select sysdate - interval '60' minute into v_result from dual;

 

[Marco Kalter]

Works fine for me. What is your exact Oracle Server version?

------------------
Marco Kalter
Allround Automations

 

[mstanton]

Oracle=8.1.7.4
pl/sql developer=5.1.0.622

[This message has been edited by mstanton (edited 24 June 2003).]

 

[Marco Kalter]

Hmm, I can't immediately find an Oracle bug for this. Still, it must be a bug, because the NUMTODSINTERVAL function is not even used in your PL/SQL code. It is probably used by the Oracle Server's PL/SQL Compiler.

------------------
Marco Kalter
Allround Automations

 

[aotte]

Errr, maybe it is me, but isn't the INTERVAL, TIMESTAMP, and TIMEZONE functionality new to 9i? I.e. not in 8?

------------------
Hakuna Matata,

Arnoud.