return size of to_char

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

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

4 thoughts on “return size of to_char”

  1. Gary

    “Oracle thinks the length is at most 42 characters. This is a bad guess”
    It doesn’t make things any clearer but the answer really is 42.

    SQL> select to_char(add_months(date ‘0001-01-01′,(-12*rownum)),’YEAR’),
    2 length(to_char(add_months(date ‘0001-01-01′,(-12*rownum)),’YEAR’))
    3 from dual;

    TO_CHAR(ADD_MONTHS(DATE’0001-01-01′,(-12*R
    ——————————————
    LENGTH(TO_CHAR(ADD_MONTHS(DATE’0001-01-01′,(-12*ROWNUM)),’YEAR’))
    —————————————————————–
    000000000000000000000000000000000000000000
    42

    Of course, it does leave open, what is the actual question….

  2. OCP Advisor

    Hi Laurent:
    The ‘Oracle Certification Professionals’ blog is a forum for candidates to share their exam preparation approach/strategy and their test taking experiences.
    I’m starting a weekly feature on the blog called “Meet the Experts”.
    With your permission, I would like to publish a Q&A session with you.
    Future candidates would greatly benefit from your advice and be encouraged to pursue a certification path.
    Please reply with your approval so that I can send the list of questions for the Q&A to you.
    Thanks in advance,
    Mohan, a.k.a. OCP Advisor
    OCP Blog – http://ebizocp.blogspot.com

  3. Nicolas

    Laurent, what version are you talking about :
    SQL> alter session set cursor_sharing='FORCE';

    Session altered.

    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

    SQL> select * from v$version where rownum=1;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    SQL>

Comments are closed.