Re: How to change the datatype of the View column?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Wed, 16 Nov 2005 18:20:44 +0000 (UTC)
Message-ID: <dlft9s$4hu$1@nntp.init7.net>

On 2005-11-16, nhcox_at_o2.pl <nhcox_at_o2.pl> wrote:
> CREATE OR REPLACE VIEW MYUSER.CLASS_VALUE
> (
> TARIFF_CLASS,
> DESCRIPTION
> )
> AS
> select substr(MYUSER.XXX_DEFAULTS.VALUE,1,3) as TARIFF_CLASS,
> MYUSER.XXX_DEFAULTS.DESCRIPTION
> from MYUSER.XXX_DEFAULTS
>
> As one can see, the TARIFF_CLASS should be varchar2(3). However,
> XXX_DEFAULTS.VALUE is vachar2(9) and therefore it is also vachar2(9).
>
> How to change the type to varchar2(3)?

First: This is not the case with Oracle 10g:

RENE> create table xyz (a varchar2(9));

Table created.

RENE> create view v1 as select substr(a,1,3) a from xyz;

View created.

RENE> desc v1
 Name Null? Type

• -------- ----------------- A VARCHAR2(3)

Otherwise, you should be able to do what you want with cast:

RENE> create view v2 as select cast(substr(a,1,3) as varchar2(4)) a from xyz;

View created.

RENE> desc v2
 Name Null? Type

• -------- -------------------------- A VARCHAR2(4)

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Wed Nov 16 2005 - 12:20:44 CST