Re: NOT NULL constrinat lost in VIEW after cast

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 23 May 2007 08:52:01 -0700
Message-ID: <1179935521.790026@bubbleator.drizzle.com>

andized_at_gmx.net wrote:
> On 22 Mai, 18:29, DA Morgan <damor..._at_psoug.org> wrote:

>> [...]
>>
>> Oracle version?
>> DDL?
>>
>> We can not look over your shoulder and see your monitor.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
>
> here's some additional information:
>
> Oracle Version:
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
>
>
> DDL of the Table:
>
> CREATE TABLE TABLE_TEST
> (
> COLUMN VARCHAR2(10 BYTE) NOT NULL,
> )
> TABLESPACE USERS
> PCTUSED 0
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> LOGGING
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
>
> DDL of the View:
>
> CREATE OR REPLACE VIEW VIEW_TEST
> (COLUMN)
> AS
> SELECT
> cast(column as number(12))
> FROM TABLE_TEST
> /
>
>
> a "DESC VIEW_TEST" gives me the following result:
>
> VIEW VIEW_TEST
> Name Null?
> Type
> ----------------------------------------- --------
> ----------------------------
> COLUMN NUMBER(12)
>
> i.e., the NOT NULL constraint is gone.
>
>
> AndiZed

If this is your DDL you have an additional issue to deal with:

SQL*Plus: Release 10.2.0.2.0 - Production on Wed May 23 08:45:25 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE TABLE_TEST
   2 (
   3 COLUMN VARCHAR2(10 BYTE) NOT NULL,    4 )
   5 TABLESPACE USERS
   6 PCTUSED 0
   7 PCTFREE 10
   8 INITRANS 1
   9 MAXTRANS 255
  10 STORAGE (

  11              INITIAL          64K
  12              MINEXTENTS       1
  13              MAXEXTENTS       2147483645
  14              PCTINCREASE      0
  15              BUFFER_POOL      DEFAULT
  16             )

  17 LOGGING
  18 NOCOMPRESS
  19 NOCACHE
  20 NOPARALLEL
  21 MONITORING;
   COLUMN VARCHAR2(10 BYTE) NOT NULL,    *
ERROR at line 3:
ORA-00904: : invalid identifier

You should NEVER name a column using a reserved word.

SELECT keyword FROM gv$reserved_words;

After cleaning up hyour code such as the comma after the NOT NULL and the other errors I did the following:

SQL> CREATE VIEW v1 AS

2 SELECT * FROM table_test;

CREATE VIEW v2 AS
SELECT CAST(zcolumn AS NUMBER(12)) AS zcolumn FROM table_test;

v1 retains the constraint and v2 does not. The issue is clearly the CAST which leads back to an original thought which is that your data type definition is incorrect.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed May 23 2007 - 10:52:01 CDT