Re: Strange effects of Cast

The original query uses the Cast functions to type the columns in a view so that the metadata of the view can be quieried by the application using it and not to truncate strings.

I personally would also prefer that the view return an error when truncating a string and therefor requiring a substring whithin the cast.

Cast(Substr(NUM1, 1, 3) As Varchar2(3)) As COL1

This fully documents what the aim of the statement.

But we have hundreds of views and it is not feasable to change each one to work around the 'bug'. I would also like to be using the newest version of Oracle. As it stands it will take some time to replace the current version.

So I would like to find a fix for this problem.

Maxim I can not find any reference to the Bug 7154415 you mention above.

As an aside I have found that the column definition

Cast('xxx' As Varchar2(3)) As COL1,
Cast(Nvl(a, b) As Varchar2(3)) As COL2,

Has an interesting side effect when a is Null and b is more than 5 characters COL2 overwrites COL1.

On 17 Feb, 03:28, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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(NUM1As Varchar2(7))            AsCOL1,
> >>    3      Cast(NUM1As Varchar2(3))            As COL2,
> >>    4      Cast(NUM1As Varchar2(9))            As COL3,
> >>    5      NUM1                                As COL4
> >>    6  From
> >>    7      (
> >>    8      Select
> >>    9          '12345678901234567890'  AsNUM1
> >>   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):
> > VARIABLENUM1VARCHAR2(20)
>
> > EXEC :NUM1:= '12345678901234567890'
>
> > COLUMN COL4 FORMAT 99999999999999999999
>
> > SELECT
> >   SUBSTR(Cast(NUM1As Varchar2(20)),1,7) AsCOL1,
> >   SUBSTR(Cast(NUM1As Varchar2(20)),1,3) As COL2,
> >   SUBSTR(Cast(NUM1As Varchar2(20)),1,9) As COL3,
> >  NUM1                                  As COL4
> > From
> >   (Select
> >     :NUM1 AsNUM1
> >   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(NUM1As Varchar2(20)),1,7) AsCOL1,
>  >   SUBSTR(Cast(NUM1As Varchar2(20)),1,3) As COL2,
>  >   SUBSTR(Cast(NUM1As 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))  AsCOL1,
>    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' asnum1from 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)) AsCOL1,
>    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- Hide quoted text -
>
> - Show quoted text -
Received on Tue Feb 17 2009 - 02:11:54 CST