Re: Strange effects of Cast

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 17 Feb 2009 03:38:56 -0800 (PST)
Message-ID: <c29951a8-07d8-4f34-99f4-382d18ffc910_at_m16g2000vbp.googlegroups.com>

On Feb 16, 10:28 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Charles Hooper schrieb:
> > 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);

> 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)

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. Received on Tue Feb 17 2009 - 05:38:56 CST