how to spell 999999999 ?


begin
dbms_output.put_line(
to_char(
timestamp '9999-12-31 23:59:59.999999999',
'FF9SP'));
end;
/
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE
THOUSAND NINE HUNDRED NINETY-NINE

Unfortunately, I could not get this in sql/10.2.0.2

select
to_char(
timestamp '9999-12-31 23:59:59.999999999',
'FF9SP') X
from
dual;
ORA-01877: string is too long for internal buffer

Well, since the string is too long, let’s try with a LONGER string 😈

select
regexp_substr(
to_char(
timestamp '9999-12-31 23:59:59.999999999',
'FF9SP/FMDAY MONTH DDTHSP YYYYSP A.D. HH24SP MISP SSSP')
,'[^/]+')X
from
dual;

X
---------------------------------------------------------
----------------------------------
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE
THOUSAND NINE HUNDRED NINETY-NINE

5 thoughts on “how to spell 999999999 ?”

  2. Bob Carlton

    Hmmmm. so does TO_CHAR for timestamps have a defect?

    —-CAST to 91 it works!—-
    SQL> select length(x) from (
    2 select
    3 CAST(to_char(timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’) as VARCHAR2(91)) x
    4 from dual);

    LENGTH(X)
    ———-
    91

    —-Cast to 90 which is shorter than resultant string. Blech!—–
    select length(x) from (
    select
    CAST(to_char(timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’) as VARCHAR2(90)) x
    from dual)

    from dual)
    *
    ORA-01877: string is too long for internal buffer

  4. coskan

    I think you have to upgrade to 10.2.0.3 to run the first code 🙂

    SQL> select version from v$instance;

    VERSION
    —————–
    10.2.0.3.0

    SQL> begin
    2 dbms_output.put_line(
    3 to_char(
    4 timestamp ‘9999-12-31 23:59:59.999999999’,
    5 ‘FF9SP’));
    6 end;
    7 /
    NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED
    NINETY-NINE

    PL/SQL procedure successfully completed.

  5. Laurent Schneider Post author

    coskan, I mean in plsql it works (also 10.2.0.2), but not in plain sql. but bob workaround to cast as varchar2(91) (or varchar2(255) to support 777777777) is great!

Comments are closed.