Geek Answers Handbook

Why does Oracle 10g to_char (date time) truncate strings?

I got an error report where Oracle 10g truncated return values ​​from to_char(datetime) :

 SQL> select to_char(systimestamp, '"day:"DD"hello"') from dual; TO_CHAR(SYSTIMESTAMP,'"DAY:"DD"HE --------------------------------- day:27hel

It is noteworthy that this does not happen in Oracle 11g . My question is: why is this happening at all? Is there some kind of configuration variable to tell to_char(datetime) to allocate a larger buffer for the return value?

+6
source share
2 answers

I'm not sure, but it might just be a mapping to SQL * Plus. Have you tried running it in a toad? Or if you assign the result of varchar2 in the PL / SQL block and the output result?

Here is what I found in the SQL * Plus Reference for 10g:

The default width and format of raw 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 in COLUMN for more information on formatting DATE columns.

Your values ​​are truncated to 9 characters, which correspond to the default A9 format. I do not have the same version and this behavior does not reproduce in 11g, so can you test my theory?

+3
source

I have the same problem and I know the solution. I am using Release 11.2.0.4.0, but I can leave this situation in other versions. It somehow depends on the client. (For example, I cannot repeat it using SQL * Plus, only with PL / SQL Devepoper) Try the following:

 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 will get the following result:

 day:08 OR any other UTF-encoded-so day:08hello

You can see that the “tagging” is lost. This exceeded 7 bytes due to 7 double-byte "yutsukeng" characters. Oracle allocates a buffer for the number of characters, not the number of bytes required. Team

 alter session set nls_length_semantics=byte/char

Unfortunately, this behavior is not affected.

So my solution is to pass the 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 casting makes the expression independent of the client or configuration. By the way, the same thing happens in all implicit to_char transforms. For instance.

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

The result can be cut off.

0
source

More articles:


All Articles