Strange effects of Cast

From: Ken <ken.clough_at_gmail.com>
Date: Mon, 16 Feb 2009 08:17:22 -0800 (PST)
Message-ID: <4a0c14bb-d02a-4393-997a-51ec9406273d_at_v15g2000yqn.googlegroups.com>

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 Received on Mon Feb 16 2009 - 10:17:22 CST