Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"?

share|improve this question
1  
Cross posted in SO: stackoverflow.com/questions/11097839/… –  Danny Varod Jun 19 '12 at 9:09
    
This sounds like a strange requirement. What problem are you really trying to solve? –  a_horse_with_no_name Jun 19 '12 at 10:33
    
Trying to mock an existing table with a view that has different data types so I can use Oracle's provider for Entity Framework to map the views/tables to entities. Oracle's provider does not support mapping "number" to "double" (only "BINARY_DOUBLE"). Some of the columns I am mocking are not nullable. An alternative may be to try and enter fake definitions into the EDMX. –  Danny Varod Jun 19 '12 at 10:52
    
And in another case I am mocking a not yet existing table, with a view that takes the partial information that already exists for other tables and one of the columns is calculated from two different columns (and mocks a PK which is of course not meant to be null). –  Danny Varod Jun 19 '12 at 10:54
    
Actually, it's not that straightforward in SQL Server if the column is part of an indexed view. –  Nick Chammas Jun 19 '12 at 17:59
add comment

1 Answer

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 +1 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
share|improve this answer
2  
Based on the SO thread, that isn't quite the issue; he has a cast column in the view, and the metadata doesn't show that it is not null. He knows it can't be null but he needs entity framework to know, apparently based on metadata. –  Alex Poole Jun 19 '12 at 16:01
    
Alex is correct. Sorry for the double post. Wasn't sure where Oracle developers/DBAs "hang out" (SO or DBA.SE). –  Danny Varod Jun 19 '12 at 16:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.