Oracle: How to create a not null column in a view

Question

Given a table like:

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL
);

I want to create a view like:

CREATE VIEW "MyView" AS
SELECT
    CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";

Only where the column "MyColumn" is "NOT NULL".

In SQL Server this is pretty straight forward:

CREATE VIEW [MyView] AS
SELECT
    ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn]
FROM [MyTable];

However the Oracle equivalent results in a "NULL" column:

CREATE VIEW "MyView" AS
SELECT
    NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn"
FROM "MyTable";

Is there anyway to force Oracle to mark the view's column as "NOT NULL"?

Answer 1

As I understand it now, you need the entity framework to see the table as having a NOT NULL BINARY_DOUBLE, but the data needs to be/is stored in a NOT NULL NUMBER. This is a problem when using a view because the view does not pass the NOT NULL attribute through when it contains a CAST.

Your options include the two Alex Poole ⁺¹ mentioned on SO (1. Virtual Column, 2. New column of different type with a trigger) or 3. New table with a different type column and a trigger. You should prefer these options in the order they are presented.

---

Previous Answer

Columns in views from columns in tables that are marked NOT NULL will inherently be NOT NULL as well. There is nothing special you need to do to make this happen.

If instead you are trying to make the view accept nulls when the table is marked as NOT NULL, then you should look into INSTEAD OF triggers. They will allow you to insert a null into a view and have it inserted as a zero into the table. Here is an example:

SQL> drop table t1;

Table dropped.

SQL>
SQL> drop view v1;

View dropped.

SQL>
SQL> create table t1 (c1 number not null);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL>
SQL> insert into t1 values (null);
insert into t1 values (null)
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LRIFFEL"."T1"."C1")


SQL>
SQL> create view v1 as select cast(c1 as binary_double) c1 from t1;

View created.

SQL>
SQL> insert into v1 values (2);
insert into v1 values (2)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


SQL>
SQL> insert into v1 values (null);
insert into v1 values (null)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


SQL>
SQL> CREATE TRIGGER TRIGGER1
  2  INSTEAD OF INSERT ON V1
  3  REFERENCING OLD AS old NEW AS new
  4  BEGIN
  5    INSERT INTO t1 VALUES (NVL(:new.c1,0));
  6  END;
  7  /

Trigger created.

SQL>
SQL> insert into v1 values (2);

1 row created.

SQL>
SQL> insert into v1 values (null);

1 row created.

SQL>
SQL> select * from t1;

        C1
----------
         1
         2
         0