Hi,
I am using Oracle SQL Developer on an 11g DB. I created a view that uses a UNION ALL to group the subset of columns of two tables together in one view. My ultimate goal is to bring the view into ADO Database First Entity Model (EDMX) on Visual Studio 2013, using ODP.NET Manager Driver.
CREATE OR REPLACE FORCE VIEW "PWS_ONLINE"."VW_ALL_REPORT" ("ROW_ID", "REPORT_ID", "REPORT_PERIOD", "EXCEL_DOC", "XML_DOC", "STATUS", "CREATED_BY", "CREATED_ON", "MODIFIED_BY", "MODIFIED_ON") AS
SELECT NVL(ROW_NUMBER() OVER (ORDER BY REPORT_PERIOD DESC, REPORT_ID DESC),0) ROW_ID,u.REPORT_ID,u."REPORT_PERIOD",u."EXCEL_DOC",u."XML_DOC",u."STATUS",u."CREATED_BY",u."CREATED_ON",u."MODIFIED_BY",u."MODIFIED_ON"
FROM
(
SELECT ('DR' + TO_CHAR(DISTRIBUTION_REPORT_ID)) AS REPORT_ID,
REPORT_PERIOD,
EXCEL_DOC,
XML_DOC,
STATUS,
CREATED_BY,
CREATED_ON,
MODIFIED_BY,
MODIFIED_ON
FROM DISTRIBUTION_REPORT
UNION ALL
SELECT ('TR' + TO_CHAR(TREATMENT_REPORT_ID)) AS REPORT_ID,
REPORT_PERIOD,
EXCEL_DOC,
XML_DOC,
STATUS,
CREATED_BY,
CREATED_ON,
MODIFIED_BY,
MODIFIED_ON
FROM TREATMENT_REPORT
) u;
ALTER VIEW VW_ALL_REPORT ADD CONSTRAINT VW_ALL_REPORT_PK PRIMARY KEY (ROW_ID) DISABLE;
Now REPORT_ID in both of these tables is a non-nullable PK, and REPORT_PERIOD, STATUS, CREATED_BY, and CREATED_ON are also non-nullable in both tables. Yet, when you view the properties of the view in Oracle SQL Developer ALL of these view columns (including the ROW_NUMBER column) incorrectly report as IsNullable = Yes. Even adding the primary key constraint had no effect. And there is seemingly no way to change the view to tell it that isNullable should be "No."
I have also tried replacing my UNION ALL by COALESCING the columns in a FULL OUTER JOIN. The result is the same. The net effect of this problem is that when you attempt to bring the view into your EDMX, it will not let you, as it complains that the primary key must contain all non-nullable columns. The only workaround I have found is really horrible in that you have to create a temp table with the same columns names and types as I have, change your view to directly query the temp table, import the view into your EDMX, and then revert the view back to its original UNION ALL syntax. Of course, if you need to add another object to your EDMX later, this will blow it up, and you have to start all over again.
This is an obvious defect of the Oracle RDMS. The view columns should not be marked as IsNullable = Yes when the columns of the source tables are not nullable. Certainly you would think a ROW_NUMBER() should be not nullable. And most definitely you would think that a column marked as a PRIMARY_KEY of the view should not be considered nullable.
I have researched this, and seen many people complaining, and yet nobody seemingly had any good answers, and Oracle didn't seem to do anything to fix it either. If somebody does know a decent workaround please do share. If not, can Oracle please come up with a solution soon. If you want .NET developers to continue to use Oracle (instead of MS SQL Server) as their backend database, it would be best to solve these issues instead of ignoring them.