I am not familiar with intervals and I misunderstand something.
I need to write a function that returns and interval comprising from current_timestamp to any other date:
Something like:
sql>select current_timestamp -(sysdate-100) from dual;
CURRENT_TIMESTAMP-(SYSDATE-100)
---------------------------------------------------------------------------
+000000100 00:00:00.152000
I have created a function:
CREATE OR REPLACE FUNCTION test_interval RETURN INTERVAL DAY TO SECOND IS
tmpVar INTERVAL DAY(0) TO SECOND(9);
BEGIN
dbms_output.put_line('Init test_interval');
--tmpVar := current_timestamp - (sysdate - 100);
select current_timestamp - (sysdate - 100)
into tmpVar
from dual;
dbms_output.put_line('End test_interval');
RETURN tmpVar;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
raise;
END test_interval;
/
And when I try it I get the ORA-01873
sql>r
1* select test_interval from dual
select test_interval from dual
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at "FERNANDO.TEST_INTERVAL", line 14
I have tried changing the leading precision for tmpVar but the result is always the same.
Can someone tell me what I am misunderstanding?