CODEWITHSUNDEEP
oracle-databasebuffertruncateto-char
theory
theory

Reputation: 9887

Why Does Oracle 10g to_char(date time) Truncate Strings?

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?

Upvotes: 6

Views: 396

Answers (2)

Naeel Maqsudov
Naeel Maqsudov

Reputation: 1434

I've got the same problem and I know the solution. I use Release 11.2.0.4.0 but I beleave it is possible to repeat the situation in other versions. It somehow depends on client. (E.g. I cannot repeat it using SQL*Plus, only with PL/SQL Devepoper) Try this:

select to_char(systimestamp, '"day:"DD"йцукенг OR any other UTF-encoded-something"') from dual
union all
select to_char(systimestamp, '"day:"DD"hello"') from dual;

You'll get the following result:

day:08йцукенг OR any other UTF-encoded-so
day:08hello

You can see the "mething" is lost. This is exactly 7 bytes exceeded because of 7 two-byte simbols "йцукенг". Oracle allocates buffer for the number of characters, not a number of required bytes. The command

alter session set nls_length_semantics=byte/char

unfortunately does not affect this behavior.

So my solution is to cast a result as varchar2(enough_capacity)

select cast(to_char(systimestamp, '"day:"DD"йцукенг OR any other UTF-encoded-something"') as varchar(1000)) from dual
union all
select to_char(systimestamp, '"day:"DD"hello"') from dual

Explicit typecasting makes expression independent from client or configuration. BTW, the same thing happens in all implicit to_char-conversions. E.g. 

case [numeric_expression]
when 1 then '[unicode_containing_string]'
end

Result might be cutted.

Upvotes: 0

Rusty
Rusty

Reputation: 2138

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?

Upvotes: 3

Related Questions