I am using Entity Framework and Oracle. Oracle's ODAC seems to have a known bug where data of type NUMBER is cast to Int64 by Entity Framework. I thought a view would assist me but
CREATE OR REPLACE VIEW test as select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual union select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual
When you look at the view the data type is Number with no precision for all of them. This appears to be a consequence of the UNION as just
CREATE OR REPLACE VIEW test as select 1 AS ONE ,CAST(2 AS NUMBER(1)) AS TWO ,CAST(3 as INT) AS THREE from dual
yields NUMBER, NUMBER(1), NUMBER as the datatypes
Is there a workaround to force Oracle to derive a precision in a view with a UNION?
Edit: @Phil asks the desired result is. He is correct that Entity Framework has to have a precision to work with so the desired datatype from the sample above would be NUMBER(1)
Edit: @Phil for this version Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - Production on Windows 32 bit a view with a union gives a view that has NUMBER with no precision. Entity Framework interprets this as int 64.
I am looking for a solution that shows the NUMBERS with a precision that Entity Framework can understand as a 32 bit integer. Any precision from NUMBER(1) to NUMBER (9) does the job.