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.

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.

share|improve this question
    
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 '12 at 14:27
    
Is it a double you're trying to get EF to cast to? –  Phil Nov 23 '12 at 14:34
    
Sorry Phil, the desired result is NUMBER(1) –  kevinsky Nov 23 '12 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 '12 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 '12 at 14:57

1 Answer 1

up vote 1 down vote accepted

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

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.