Re: Strange effects of Cast

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 17 Feb 2009 04:28:10 +0100
Message-ID: <499A2ECA.8000705_at_gmail.com>



Charles Hooper schrieb:
> 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.

Charles, in this context i don't see any benefit in using bind variables

  • i get identical results with > 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
for all inputs - literals, column values and bind variable. I have impression, the Michael's suggestion describes this behaviour most adequately - cast treats its arguments as passed by reference instead of passed by value. To confirm that, small example with expression as arguments (on 10.2.0.4)

SQL> Select

   2      Cast(SUBSTR (NUM1,1) As Varchar2(7))  As COL1,
   3      Cast(SUBSTR (NUM1,1) As Varchar2(3))  As COL2,
   4      Cast(SUBSTR (NUM1,1) As Varchar2(9))  As COL3,
   5      NUM1                                 As COL4
   6  From (
   7      select '12345678901234567890' as num1 from dual
   8 )
   9 /

COL1 COL COL3 COL4

------- --- --------- --------------------
1234567 123 123456789 12345678901234567890

The same result with almost any other expression (like trim(),replace(),etc., the expression should be of course one which doesn't change the input value).

Unexpected for me was the case with concatenation however 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 t
   7 ;
 From t

      *
ERROR at line 6:
ORA-01489: result of string concatenation is too long

On the other side, i can't see in documentation any description of casting strings with loss of precision. Hence, i would prefer, if it wouldn't be allowed ( like it is for numbers)

SQL> select cast(123 as number(2)) from dual; select cast(123 as number(2)) from dual

             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Best regards

Maxim Received on Mon Feb 16 2009 - 21:28:10 CST

Original text of this message