The width of a columns is known before execution.
Well, in most cases…
SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH')
FROM DUAL;
TO_CHAR(
--------
SAMEDI
The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.
SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT
Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.
SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED
EIGHTY-EIGHT
again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.
let’s try with spelling the nanoseconds :
select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION
here 78 is a really bad guess… the nanoseconds could be 100 character long !
What happened then :
select to_char(timestamp '0001-01-01 00:00:00.777777777',
'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer
The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!
It simply means Oracle sets the width column to something too small to contain the result.
By setting the column to something longer, the query should work…
select cast(to_char(timestamp '0001-01-01 00:00:00.
777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN
Ok, let’s do something perverse 
Let set the CURSOR_SHARING to FORCE !
Have a look :
SQL> alter session set cursor_sharing='FORCE';
Session altered.
SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION
the width of the column is evaluated to 75. 75 however is not the maximum length!
SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01801: date format is too long for internal buffer
Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.
Let’s revert to EXACT for confirmation.
SQL> alter session set cursor_sharing='EXACT';
Session altered.
SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION
SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED
SEVENTY-SEVEN
