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!

numtodsinterval and ORA-01873

AlbertoFaenzaMay 23 2017 — edited May 24 2017

Hi,

I have a question regarding the function numtodsinterval.

Database version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

"CORE    12.2.0.1.0    Production"

TNS for Linux: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

If I write the following statement I'm able to get an interval having 9 days precision:

select numtodsinterval(999999999,'day') intval

  from dual;

INTVAL                   

--------------------------

+999999999 00:00:00.000000

This is in line with Oracle documentation: NUMTODSINTERVAL which says:

By default, the precision of the return is 9.

A higher number of days (with 10 digits) will return error:

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

Now if I try to convert the corresponding hours (999999999 * 24 ) to an interval:

select numtodsinterval(999999999*24,'hour') intval

  from dual;

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

Isn't the resulting interval having the same precision? DAY(9) TO SECOND(6) ?

Can someone explain me the reason of this behavior?

Do I misunderstand the sentence "By default, the precision of the return is 9." in the documentation?

Regards,

Alberto

This post has been answered by Paulzip on May 23 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2017
Added on May 23 2017
12 comments
1,233 views