oracle leading6,ORA-01873 Leading Precision Too Small

Bug. You need to create a subtype:

SQL> create or replace

2 FUNCTION hrmin_interval_string(val INTERVAL DAY TO SECOND)

3 RETURN VARCHAR2 IS

4 hrs NUMBER(6);

5 mins NUMBER(2);

6 BEGIN

7 hrs := EXTRACT(DAY FROM val)*24 + EXTRACT(HOUR FROM val);

8 mins := EXTRACT(MINUTE FROM val);

9

10 RETURN hrs || ':' || TO_CHAR(mins, '00');

11 END;

12 /

Function created.

SQL> exec dbms_output.put_line(RPAD(hrmin_interval_string('+000105 06:56:00.000000'), 12));

BEGIN dbms_output.put_line(RPAD(hrmin_interval_string('+000105 06:56:00.000000'), 12)); END;

*

ERROR at line 1:

ORA-01873: the leading precision of the interval is too small

ORA-06512: at line 1

SQL> create or replace

2 package pkg1

3 is

4 subtype unconstrained_ds is interval day(9) to second(9);

5 end;

6 /

Package created.

SQL> create or replace

2 FUNCTION hrmin_interval_string(val pkg1.unconstrained_ds)

3 RETURN VARCHAR2 IS

4 hrs NUMBER(6);

5 mins NUMBER(2);

6 BEGIN

7 hrs := EXTRACT(DAY FROM val)*24 + EXTRACT(HOUR FROM val);

8 mins := EXTRACT(MINUTE FROM val);

9

10 RETURN hrs || ':' || TO_CHAR(mins, '00');

11 END;

12 /

Function created.

SQL> exec dbms_output.put_line(RPAD(hrmin_interval_string('+000105 06:56:00.000000'), 12));

PL/SQL procedure successfully completed.

SQL>SY.