Force number precision in a view with Union

oracleoracle-11g-r2

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

Best Answer

If you say 

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

gives you what you want, why not use the same for union :

CREATE OR REPLACE VIEW test_v11 as
SELECT ONE, CAST(TWO AS NUMBER(1)) AS TWO, CAST(THREE AS INT) AS THREE
FROM 
(
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
  UNION
 SELECT 1 AS ONE ,2 AS TWO ,3 AS THREE FROM dual
 )a
Related Question