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
Hi,
Good Solution…!!!
Regards
Taj
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
I find also strange that PL/SQL reacts differently than SQL for the same function
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.
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!