Last post Jul 31, 2013 01:55 PM by agent_smith
Jul 30, 2013 12:05 PM|agent_smith|LINK
I have two data sources. SQL and Oracle.
From SQL I get a list of about 2,000 items.
I want to get back items from my Oracle database, JOINed to the keys they share.
The Oracle database has millions of rows, so fetching it all is not an option.
Is there some way, using OracleDataReader or similar that I can join my LINQ SQL list to the Oracle query and get back only the 2,000 matching items?
Jul 30, 2013 04:34 PM|Lannie|LINK
sure with most modern ODP.NET drivers (11g ODAC Release 4 or Release 5, 12c ODAC Release 1)
best support is with Unmanaged Driver,
Managed driver is relatively new just emerging from beta, and all the support is not yet there.
Jul 31, 2013 07:43 AM|agent_smith|LINK
Links to other sites to get the driver do not really help.
Of course I am able to find the Oracle web site on my own and download. I am a month past that.
The question is about using a join statement in LINQ (or a similar strategy) to join to different data sources, one being Oracle.
I appreciate the effort, though.
Jul 31, 2013 11:53 AM|Lannie|LINK
Put your question here:
more advanced ODP.NET users visit there too.
Jul 31, 2013 01:55 PM|agent_smith|LINK
I have learned a lot about using an Array type as a SELECT parameter to form basically a 'WHERE-IN' clause.
string cmdText = "select from SOMETABLE where ID IN (SELECT * FROM TABLE(CAST(:myarrayids AS TABLE)))";
param = new OracleParameter();
param.ParameterName = "myarrayids";
param.OracleDbType = OracleDbType.Varchar2;
param.Value = test;
oCmd = new OracleCommand(cmdText, connection);
oCmd.ArrayBindCount = test.Length;
oRd = oCmd.ExecuteReader();
However this results in 'cannot access rows from a non-nested table
Which I am still researching.
Basically if you concatonated a string to do a 'where in' clause, you are limited to 1,000 items. I have more than that.
So I need to contruct something like an Array variable containing my 2,000+ values, then do a SELECT statement with a 'WHERE X IN (SELECT COLUMNFROMARRAY FROM :ARRAYPARAMETER)
But this does not quite work yet.