Re: Strange effects of Cast

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 16 Feb 2009 18:22:42 -0800 (PST)
Message-ID: <96124c54-3f3b-4e0e-a987-6f8c43845ab6_at_m4g2000vbp.googlegroups.com>

On Feb 16, 3:45�pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> It seems to be related to Bug 7154415.
> On 11.1.0.7 results are like what you are expecting to be:
>
> SQL> select * from v$version;
>
> BANNER
> ---------------------------------------------------------------------------�-----
> Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> PL/SQL Release 11.1.0.7.0 - Production
> CORE � �11.1.0.7.0 � � �Production
> TNS for Linux: Version 11.1.0.7.0 - Production
> NLSRTL Version 11.1.0.7.0 - Production
>
> SQL> Select
> � �2 � � �Cast(NUM1 As Varchar2(7)) � � � � � �As COL1,
> � �3 � � �Cast(NUM1 As Varchar2(3)) � � � � � �As COL2,
> � �4 � � �Cast(NUM1 As Varchar2(9)) � � � � � �As COL3,
> � �5 � � �NUM1 � � � � � � � � � � � � � � � � As COL4
> � �6 �From
> � �7 � � �(
> � �8 � � �Select
> � �9 � � � � �'12345678901234567890' �As NUM1
> � 10 � � �From
> � 11 � � � � �Dual
> � 12 � � �)
> � 13 �/
>
> COL1 � �COL COL3 � � �COL4
> ------- --- --------- --------------------
> 1234567 123 123456789 12345678901234567890
>
> Despite that, i agree with Charles, the proper tool for truncation of
> strings is the SUBSTR functions family, if you misuse the CAST for that
> purpose, you are buing side effects, such as implicit conversions etc.
>
> Best regards
>
> Maxim

Maxim,

Thanks for the follow up with the more recent version of Oracle.

I think that it has been stated a couple times, in various places, that using bind variables is generally a good idea. Let's see if Oracle agrees (executed in SQL*Plus):
VARIABLE NUM1 VARCHAR2(20) EXEC :NUM1 := '12345678901234567890'

COLUMN COL4 FORMAT 99999999999999999999

SELECT

  SUBSTR(Cast(NUM1 As Varchar2(20)),1,7) As COL1,
  SUBSTR(Cast(NUM1 As Varchar2(20)),1,3) As COL2,
  SUBSTR(Cast(NUM1 As Varchar2(20)),1,9) As COL3,
  NUM1                                   As COL4

From
  (Select
    :NUM1 As NUM1
  From
    Dual);

COL1 COL COL3 COL4

------- --- --------- --------------------

1234567 123 123456789 12345678901234567890

SELECT
  VERSION
FROM
  V$INSTANCE; VERSION

---

10.2.0.4.0

The solution, or at least a work around, is to try submitting the value in a bind variable. A better idea would be to use the SUBSTR function.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Feb 16 2009 - 20:22:42 CST