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 :twisted:


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 ?”

  1. 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

  2. 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.

  3. 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!

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>