Oracle number format model maximum length

oracle, oracle11g, sql

Solution

It's beyond precision NUMBER can go. More info in official documentation: http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements001.htm#SQLRF00222

Problem

I run into a peculiar limit in `X` number format element used in `to_number`and in `to_char` when I was converting hexadecimal numbers to decimal numbers. It looks like number format model has maximum length of 63 characters but I can't find the limit mentioned in the docs. E.g. the maximum length of datetime format model is mentioned: The total length of a datetime format model cannot exceed 22 characters. With 63 `X`es: ``` SQL> select to_number('A', rpad('X', 63, 'X')) from dual; TO_NUMBER('A',RPAD('X',63,'X')) ------------------------------- 10 ``` With 64 `X`es: ``` SQL> select to_number('A', rpad('X', 64, 'X')) from dual; select to_number('A', rpad('X', 64, 'X')) from dual * ERROR at line 1: ORA-01481: invalid number format model ``` I have only tried `X` format element as it is relevant for my current task. The questions: - Is this a known number format model limitation that is just missing from documentation ? - How do I convert a 64 (and longer) digit length hexadecimal number to a decimal number ?

Original source

Related problems