I got a bug report where Oracle 10g was truncating return values from to_char(datetime):
SQL> select to_char(systimestamp, '"day:"DD"hello"') from dual;
TO_CHAR(SYSTIMESTAMP,'"DAY:"DD"HE
---------------------------------
day:27hel
Notably, this does not appear to happen in Oracle 11g. My question is, why does it happen at all? Is there some configuration variable to set to tell to_char(datetime) to allocate a bigger buffer for its return value?
I'm not sure but it might be just displaying in SQL*Plus. Have you tried to run it in Toad? Or if you assign result to varchar2 in PL/SQL block and output result?
Here what I've found in SQL*Plus Reference for 10g:
The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9. See the FORMAT clause of the COLUMN command for more information on formatting DATE columns.
Your values is trimmed to 9 characters which corresponds to default A9 format. I don't have same version and this behaviour is not reproducing in 11g so can you please check my theory?