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.