CODEWITHSUNDEEP
oracle-databaseoracle10g
Teejay
Teejay

Reputation: 7481

Strange behavior on Oracle CAST to NVARCHAR2

I have the following query:

SELECT (CAST("META_SECT_ORDER" AS NVARCHAR2(3)) || '#' || "CODE") AS "SECTION"
FROM "BMAN_TP2"."LOADER_TEMPLATE_SECTIONS"

META_SECT_ORDER is obviously in integer, while CODE is a string.

It outputs 700 rows like these:

SECTION
-------
 0#F01
 0#I05
 1#I05
 2#I05

etc...

I know that CAST is not necessary but the query is composed by an underlying querybuilder that also outputs for MsSQL Server (where the CAST is needed) and Postgres.

The strange fact is that if I raise the NVARCHAR2 length I obtain different results. For example:

What's that??

EDIT :

Actually, It seems that it depends only on the CAST, the concatenation is not relevant.

Upvotes: 5

Views: 21734

Answers (1)

Jon Heller
Jon Heller

Reputation: 36912

This looks like "Bug 9949330 - ORA-7445 or garbled data casting a NUMBER to NVARCHAR2". You've already discovered the official work-around, use values <= 80.

You should contact support to either download the patch or request one for your platform.

Here's an easier way to reproduce the issue without using any of your data. It still fails as of 11.2.0.3.

SQL> select cast(level as nvarchar2(130)) from dual connect by level <= 1;

CAST(LEVELASNVARCHAR2(130))
--------------------------------------------------------------------------------
ååååååååå┐┐┐ ┐┐  ┐┐A ┐┐  ┐┐A ┐┐  A   ┐┐  ┐   ┐┐  ┐┐A

Upvotes: 4

Related Questions