6

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?

theory
  • 9,178
  • 10
  • 59
  • 129
  • I'm not seeing that in 10.2.0.5. Which patch level (and maybe platform) are you seeing this on? – Alex Poole Sep 29 '16 at 17:38
  • The [OP](https://github.com/theory/sqitch/issues/316) says, "Oracle Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production." – theory Sep 29 '16 at 17:44
  • 1
    It seems that Rusty's answer below is correct. There are *no any parameter related to "buffer allocation"* in Oracle for such functions. Only if result exceeds expected result type (e.g. 4000 for varchar2 like here) you'll get an exception. I red examples given in your "bug report" and can see the result depends on the length of expression. So it looks like default behaviour of SQL Plus for unnamed columns. Result trimmed on displaying. Try to give an alias to the column and use COLUMN command to set a proper width. See https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm – Naeel Maqsudov Oct 30 '16 at 12:05

2 Answers2

3

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?

Rusty
  • 1,988
  • 10
  • 12
  • Thanks, trying to get the [OP to confirm](https://github.com/theory/sqitch/issues/316#issuecomment-258007161). – theory Nov 07 '16 at 18:46
0

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.

Naeel Maqsudov
  • 1,352
  • 14
  • 23
  • Was *really* hoping this would work, but [apparently not](https://github.com/theory/sqitch/issues/316#issuecomment-262443669). :-( – theory Nov 23 '16 at 17:30