I am getting unexpected results from a Select statement which uses
Cast to tuncate strings.

In the query below although I get the expected result in COL1 and COL2
COL3 and COL4 return only three characters. In fact if I swap COL1 and
COL2 in the query then all columns are three characters long.

I have not been able to find any references to this problem. Has
anyone else come accross this behavior?

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(
Select
'12345678901234567890' As NUM1
From
Dual
)


COL1 COL COL3 COL4
------- --- --------- --------------------
1234567 123 123 123
1 row selected.

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

On Feb 16, 11:17*am, Ken <ken.clo... (AT) gmail (DOT) com> wrote:
It seems like something similar was reported to the group a year or
two ago. I don't remember what the cause was determined to be, but I
believe that it has to do with the data not residing in an actual
table, so the "before" picture of the generated column is lost after
the first call to CAST.

I am a little suprised that the above experiment did not throw an
error. It appears that you are mis-using the CAST function:
http://download.oracle.com/docs/cd/B...ctions016..htm

Oracle 10.2.0.4 returns the same results:
SELECT
VERSION
FROM
V$INSTANCE;

VERSION
----------
10.2.0.4.0

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(
Select
'12345678901234567890' As NUM1
From
Dual
);

COL1 COL COL3 COL4
------- --- --------- ----
1234567 123 123 123

Now, let's try the same experiment with the source data residing in a
table:
CREATE TABLE T1 AS
Select
'12345678901234567890' As NUM1
From
Dual;

COMMIT;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T1;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4
--------------------
12345678901234567890 12345678901234567890 12345678901234567890
12345678901234567890

The above did not give you the expected results?

Let's look at the table description:
DESC T1

Name Null? Type
-------- -------- --------
NUM1 CHAR(20)

A CHAR column, not a VARCHAR2, was created, let's try another
experiment, this time forcing a VARCHAR2 column to be created in the
table:
CREATE TABLE T2 AS
Select
CAST('12345678901234567890' AS VARCHAR(20)) As NUM1
From
Dual;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T2;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4
--------------------
12345678901234567890 12345678901234567890 12345678901234567890
12345678901234567890

The above did not give you the expected results, same as before?

Let's try again, this time starting with a numeric value:
Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(
Select
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual
);

ERROR at line 3:
ORA-25137: Data value out of range

So, what is Oracle supposed to do with the last 13 digits of the
number? Maybe it will help if the data is in a table?
CREATE TABLE T3 AS
Select
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T3;

ERROR at line 2:
ORA-25137: Data value out of range

Still did not produce the desired result?

Let's try again, this time providing sufficient space for the CAST
call, and then using SUBSTR to pick up only the characters of
interest:
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
T3;

COL1 COL COL3 COL4
------- --- --------- ----------
1234567 123 123456789 1.2346E+19

I have a feeling that the above is *almost* what you want to see.

COLUMN COL4 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
T3;

COL1 COL COL3 COL4
------- --- --------- ---------------------
1234567 123 123456789 12345678901234567890

The above is probably what you want.

Or, directly from DUAL:
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
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual);

COL1 COL COL3 COL4
------- --- --------- ---------------------
1234567 123 123456789 12345678901234567890

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Ken schrieb:
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 Demenko wrote:
He is, after all, running an obsolete, non-supported version... You get
what you pay for?


in 10.2.0.1 XE - you get:
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 );

COL1 COL COL3 COL4
------- --- --------- --------------------
1234567 123 123 123



and thinking about it, I would tend to lean towards this actually being
the correct result as you change NUM1 with the first and subsequent
statements.

If you do perl or php or basic or whatever try this:

psuedo-code:
$x=12345678901234567890;
$x=left($x,7);
$col1=$x;
$x=left($x,3);
$col2=$x;
$x=left($x,9);
$col3=$x;
$col4=$x;
echo "col1 col2 col3 col4";
echo "-------------------------------------------------------";
echo $col1." ".$col2." ".$col3." ".$col4;

But as stated, this is fixed in some release after 10.2.0.1.

On Feb 16, 3:45*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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 Hooper schrieb:
Charles, in this context i don't see any benefit in using bind variables
- i get identical results with
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

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... (AT) gmail (DOT) com> wrote:

Here is an example of more column interaction with Cast and Nvl
including a workaround

CREATE TABLE T1 AS
Select
Cast('12345678901234567890' As Varchar(12)) As NUM1,
Cast('abcdefghijklmnopqrst' As Varchar(12)) As CHR1,
Cast(Null As Varchar(12)) As NUL1
From
Dual;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(9)) As COL2,
Cast(Nvl(NUL1, CHR1) As Varchar2(4)) As COL3,
NUM1 As COL4
From
T1;

Select
Cast(Substr(NUM1, 1, 7) As Varchar2(7)) As COL1,
Cast(Substr(NUM1, 1, 9) As Varchar2(9)) As COL2,
Cast(Substr(Nvl(NUL1, CHR1), 1, 4) As Varchar2(4)) As COL3,
NUM1 As COL4
From
T1;


Drop Table T1;

Table created.

Example 1
COL1 COL2 COL3 COL4
------- --------- ---- ------------
1234567 ijkl567 abcd 1234567
1 row selected.

Example 2
COL1 COL2 COL3 COL4
------- --------- ---- ------------
1234567 123456789 abcd 123456789012
1 row selected.

As you can see in Example 1 COL2 has not only the wrong length but
also has been partially overwritten by COL3.

Using Substr to size the columns before casting them works around the
problem. Still not a good situation to be in. I am wondering what else
is being affected.

Ken

On Feb 16, 10:28*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
Maxim,

I realized about 30 seconds after posting that I did not use the
original SQL statement when I experimented with bind variables, that
instead I used the final suggestion of:
SUBSTR(Cast(NUM1 As Varchar2(20)),1,7)
Rather than Cast(NUM1 As Varchar2(7))

When I noticed the mistake, I tested again with the original SQL
statement and reached the same conclusion as you state above. I
attempted to delete the post, but it appears that Google's posting
service does not propogate the delete request (at least not fast
enough).

Thanks for catching the mistake.

SELECT
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(Select
:NUM1 As NUM1
From
Dual);

COL1 COL COL3 COL4
------- --- --------- ----
1234567 123 123 123

PRINT NUM1

NUM1
--------------------
12345678901234567890

Try #2:
WITH V1 AS
(Select
:NUM1 As NUM1
From
Dual)
SELECT
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
v1;

COL1 COL COL3 COL4
------- --- --------- ----
1234567 123 123 123

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Feb 16, 2:45*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
It appears the fix is in the 11.1.0.7 patchset, as 11.1.0.6 produces
the same results as the OP provided:


SQL> with src as(
2 Select
3 '12345678901234567890' As NUM1
4 From
5 Dual
6 )
7 Select
8 NUM1 As COL4,
9 Cast(NUM1 As Varchar2(7)) As COL1,
10 Cast(NUM1 As Varchar2(3)) As COL2,
11 Cast(NUM1 As Varchar2(9)) As COL3
12 From src;

COL4 COL1 COL COL3
-------------------- ------- --- ---------
123 1234567 123 123

SQL> set autotrace on
SQL> /

COL4 COL1 COL COL3
-------------------- ------- --- ---------
123 1234567 123 123

Using the query directly in the cast statement provides the desired
output:

SQL> Select
2 (
3 Select
4 '12345678901234567890' As NUM1
5 From
6 Dual
7 ) as COL4,
8 cast((
9 Select
10 '12345678901234567890' As NUM1
11 From
12 Dual
13 ) as varchar2(7)) COL1,
14 cast((
15 Select
16 '12345678901234567890' As NUM1
17 From
18 Dual
19 ) as varchar2(3)) COL2,
20 cast((
21 Select
22 '12345678901234567890' As NUM1
23 From
24 Dual
25 ) as varchar2(9)) COL3
26 From dual;

COL4 COL1 COL COL3
-------------------- ------- --- ---------
12345678901234567890 1234567 123 123456789

SQL>


David Fitzjarrell