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!

ORA-01873 Leading Precision Too Small

DBeltJun 2 2010 — edited Jul 26 2011
I am getting an ORA-01873 "leading precision of the interval is too small" error and can't figure out why. The error is occurring on a call to the following function (please excuse any syntax errors since I have to fat finger all of my entries):
FUNCTION hrmin_interval_string(val INTERVAL DAY TO SECOND)
RETURN VARCHAR2 IS
  hrs   NUMBER(6);
  mins  NUMBER(2);
BEGIN
  hrs := EXTRACT(DAY FROM val)*24 + EXTRACT(HOUR FROM val);
  mins := EXTRACT(MINUTE FROM val);

  RETURN hrs || ':' || TRIM(TO_CHAR(mins, '00'), 12);
END;
The code that calls this function is as follows:
...
  time_delta    INTERVAL DAY(6) TO SECOND;
  tstr          VARCHAR2(500);
...
  tstr := RPAD(hrmin_interval_string(time_delta), 12);
The value of time_delta when the error occurs is +000105 06:56:00.000000.

This value of time_delta is well within the precision and should not be causing this error, at least as far as I can tell. Does anybody see what I am missing?

I tried specifying INTERVAL DAY(6) TO SECOND in the function parameter list but that just causes a compile error saying it has encountered the "(" character when it is expecting "TO".

I am running Oracle 11g.
This post has been answered by Solomon Yakobson on Jun 2 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 23 2011
Added on Jun 2 2010
5 comments
16,623 views