Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

need help with interval

411930Oct 16 2007 — edited Oct 17 2007
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2007
Added on Oct 16 2007
8 comments
1,157 views