This content has been marked as final. Show 14 replies
I still need help solving this problem. Is there anybody from Oracle out there who knows how to solve this? Is it a bug or is there any known workaround for it?
Perhaps the documentation is incorrect? The maximum value of NUMBER(10,0) (9,999,999,999) is greater than the maximum value of INT32 (2,147,483,647), so it would make sense that it's an INT64.
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.
Just wondering if anyone solved this problem? I am also hitting this problem and it is causing me great pain.
Is there any update available? Where can I track the bug myself? I still have this problem (after months). I created a workaround 3 months ago, but the database size increased exponentially and now each query is taking more then 20 minutes (compared to 5 to 10 seconds on MS SQL server...).
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.
If the new ODP.NET version doesn't fix the bug for you, you're likely encountering a different bug then. My recommendation is to open a service request with Oracle Support to diagnose the bug and then Oracle can fix it for you.
Thank you for your reply.
I submitted a service request. I will post the outcome here when (if) it comes available.
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.
I found the difference.
In your test, ID is a key column. In mine, it is not.
Yes, when ID is a key column, the error is reproduced.
<PropertyRef Name="ID" />