This content has been marked as final. Show 14 replies
One workaround is to use Decimal.
var sql = "SELECT * FROM MEMBER";
var members = ctx.ExecuteStoreQuery<decimal>(sql).ToList();
foreach (decimal val in members)
Most likely, you are running into Bug 13559540, which is also described here:
Bug: ExecuteStoreQuery does not map correctly (ODP.NET 184.108.40.206.50 Beta 3)
There is a fix for this bug and it is being incorporated in an upcoming Oracle patch.
Thank you for the reply.
I can't say if it is the same problem. It looks a bit like it though.
I don't know how to apply your workaround. My context is of the type System.Data.Entity.DbContext and doesn't have an ExecuteStoreQuery option.
My context is generated by the ADO.NET Entity Data Model wizard, as described in http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm
Is there a way I get informed when the patch is available? I'm now just using EF to get all the data, which is a lot slower, but it works for the moment.
There is no mechanism for being informed when Oracle patches are released. They are generally on a regular release schedule. For example, Oracle patchsets are released generally once a year. The last 11.2 patchset was released around the end of last year, which puts the next scheduled patchset at around the end of this year. The bug fix is being incorporated into that Oracle patchset, 220.127.116.11.
If there is an urgent customer need for a fix sooner than that, customers can request the next bundled patch (every 3-4 weeks) or a one-off patch to be released.
The bug was patched back in June as part of the Oracle DB 18.104.22.168 Patch 7 on My Oracle Support. For your reference, all bugs and their status can be tracked by logging in to My Oracle Support (MOS).
Your DBA should either be able access MOS or provide you access.
However, today is the day we released a new ODAC version on OTN, which has all fixes up to and including Patch 10. You can download that version here:
Where can I find this bug in support.oracle.com? I've got a MOS account, but can't find it in there.
Furthermore, I've installed the new 22.214.171.124.20 driver, but still have the same problem.
I've updated the references to orace.dataaccess.dll and updated the Model.edmx from the database. Is there anything more I should do?
The only way I get this to work is to manually edit the edmx, change the Precision of the number fields from 10 to 11, the entity types from Int32 to In64 and cast every long to int in my application. But this is offcourse a very "dirty" and potential dangerous method.
Using ODAC 11.2 Release 5 (126.96.36.199.20), I cannot reproduce the error you described.
Or maybe I have missed something.
1. created a test table
create table member (c1 number(1) primary key, id number(10) not null);
2. Insert test data
insert into member values(1, 1234);
insert into member values(2, 4567);
3. Use the EDM wizard to create a model based on member table.
No custom type mapping in the app.config.
CSDL section in the edmx file:
<PropertyRef Name="C1" />
<Property Name="C1" Type="Int16" Nullable="false" />
<Property Name="ID" Type="Int32" Nullable="false" />
4. Test code
using (Entities ctx = new Entities())
var sql = "SELECT * FROM MEMBER";
var members = ctx.MEMBERs.SqlQuery(sql).ToList();
foreach (var v in members)
Edited by: shsu on Sep 20, 2012 3:48 PM
It looks if you do exactly the same as me. I can't tell why is does work with your setup. There must be some small difference.
I created a ticket at Oracle support with a complete .net solution and DDL file to reproduce the problem. Jenny at Oracle support was very helpful and tested everything. She was able to reproduce the problem and filed a bug for me: Bug 14642733 - EF: CALLING SQLQUERY.TOLIST() THROW INVALIDCASTEXCEPTION
So far I'm really pleased with all your help. I will update this thread for reference usage if I get an update to this bug report.
For now I created a workaround using T4 files to generate BulkLoad functions that use a oracaledatareader and creates and fills Entity instances with all the data. It's not perfect as I can't use lamba expressions for the selections or use joins. But I'm now at least able to fetch large amounts of data.