Re: Column width different in view

From: Dennis Williams <oracledba.williams_at_gmail.com>
Date: Mon, 18 Jul 2011 09:36:21 -0500
Message-ID: <CAGRGHjxHMDMZwk+djihSL5hb-GZWDQF7sK9tPFjOTqjZ3fcpfQ_at_mail.gmail.com>

Thanks everyone. Wow, I never would have guessed this one. Amazing resources on this list.

On Sat, Jul 16, 2011 at 11:19 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Very likely that is it. Graham Wood clued me in to using the cast
> operator to get the result you want, but understand that the default the
> database chooses is going to be safe for the possible content under
> conversion rules (notice that 18*3 is 54, which also happens to be 6*9 and
> the answer to everything once you realize that the famous 42 is base
> thirteen.) But I digress. If you were going potentially from a single byte
> character encoding to a possibly 3 byte encoding per character, then the
> database is protecting you in this view creation. Perhaps the one database
> where you experience this problem is single byte character encoding like
> ASCII7 and the others are not. If you know that trouble can’t happen in your
> context then casting it back to 18 should not be a problem.****
>
> ** **
>
> Example:****
>
> ** **
>
> SQL> desc dual****
>
> Name
> Null? Type****
>
> -----------------------------------------------------------------------------
> -------- -------------****
>
> DUMMY
> VARCHAR2(1)****
>
> ** **
>
> SQL> create view vdual as (select cast(dummy as varchar2(1)) smartie from
> dual);****
>
> ** **
>
> View created.****
>
> ** **
>
> SQL> desc vdual****
>
> Name
> Null? Type****
>
> -----------------------------------------------------------------------------
> -------- -------------****
>
> SMARTIE
> VARCHAR2(1)****
>
> ** **
>
> SQL> select * from vdual;****
>
> ** **
>
> S****
>
> -****
>
> X****
>
> ** **
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Bobak, Mark
> *Sent:* Friday, July 15, 2011 6:09 PM
> *To:* Dennis Williams; oracle-l_at_freelists.org
> *Subject:* Re: Column width different in view****
>
> ** **
>
> What is nls length semantics set to? What is character set?
>
>
>
> *Sent from my Motorola ATRIX™ 4G on AT&T*****
>
>
>
> -----Original message-----****
>
> *From: *Dennis Williams <oracledba.williams_at_gmail.com>*
> To: *"oracle-l_at_freelists.org" <oracle-l_at_freelists.org>*
> Sent: *Fri, Jul 15, 2011 22:04:24 GMT+00:00*
> Subject: *Column width different in view****
>
> List,****
>
> ****
>
> We have a situation where a view is created on a table. The underlying
> column is defined as CHAR(18). We are doing this to a number of Oracle
> 10.2.0.4 databases. On all of them the view creates just fine. But on one
> database the column in the view is created as CHAR(54). Has anyone
> encountered anything like this?****
>
> ****
>
> Thank you,****
>
> Dennis Williams****
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 18 2011 - 09:36:21 CDT