RE: Column width different in view
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxx>, "'Dennis Williams'" <oracledba.williams@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 16 Jul 2011 12:19:33 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Bobak, Mark
Sent: Friday, July 15, 2011 6:09 PM
To: Dennis Williams; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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
Other related posts:
