0

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.

6
  • So, you're trying to get Entity Framework to cast to a Double? Have you seen the table on this page? devart.com/dotconnect/oracle/docs/DatatypeMapping.html The NUMBER() has to have a scale for Entity Framework to cast appropriately.
    – Philᵀᴹ
    Nov 23, 2012 at 14:27
  • Is it a double you're trying to get EF to cast to?
    – Philᵀᴹ
    Nov 23, 2012 at 14:34
  • Sorry Phil, the desired result is NUMBER(1)
    – kevinskio
    Nov 23, 2012 at 14:36
  • Yes, but I'm looking at the bigger picture. EF is casting to an Int64 - What data type do you actually want it to cast to?
    – Philᵀᴹ
    Nov 23, 2012 at 14:43
  • 1
    Also, I get NUMBER, NUMBER(1), NUMBER(38) for the datatypes when trying your UNION test view on 11.2.0.2.0 64-bit Linux.
    – Philᵀᴹ
    Nov 23, 2012 at 14:57

1 Answer 1

2

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
3
  • I am away from my desk but that looks interesting. What is the precision when you run it?
    – kevinskio
    Nov 23, 2012 at 22:23
  • @kevinsky: the second view shows the same precision as the first - ` NUMBER, NUMBER(1), NUMBER `
    – a1ex07
    Nov 25, 2012 at 18:04
  • good answer, using CAST on the overall select works fine and gives a view with the column precision of NUMBER(1).
    – kevinskio
    Nov 25, 2012 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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