Re: NOT NULL constrinat lost in VIEW after cast

On 23 Mai, 17:52, DA Morgan <damor..._at_psoug.org> wrote:
> andi..._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
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
> 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;
>

thanks for the advice. my "real" table uses proper fieldnames of course. i just tried to post a simplified version where i unluckily added this error.

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

i see.
so a CAST in a VIEW always removes the NOT NULL constraint, i suppose because the CAST could theoretically create NULL values.

thanks for your help,
AndiZed Received on Thu May 24 2007 - 05:40:34 CDT