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