Re: Strange effects of Cast
- From: Ken <ken.clough@xxxxxxxxx>
- Date: Tue, 17 Feb 2009 00:11:54 -0800 (PST)
Thanks to all for your responses...
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...@xxxxxxxxx> wrote:
Charles Hooper schrieb:
On Feb 16, 3:45 pm, Maxim Demenko <mdeme...@xxxxxxxxx> 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 -
.
- Follow-Ups:
- Re: Strange effects of Cast
- From: Ken
- Re: Strange effects of Cast
- References:
- Strange effects of Cast
- From: Ken
- Re: Strange effects of Cast
- From: Maxim Demenko
- Re: Strange effects of Cast
- From: Charles Hooper
- Re: Strange effects of Cast
- From: Maxim Demenko
- Strange effects of Cast
- Prev by Date: Re: Strange effects of Cast
- Next by Date: Re: Several rows from Dual
- Previous by thread: Re: Strange effects of Cast
- Next by thread: Re: Strange effects of Cast
- Index(es):
Relevant Pages
|
