Today Joseph was creating a windows service, which had an schedule task to execute something on an Oracle Server.
Everything worked out of the Windows Service but as soon as he added it to the Windows Service, it fail.
He suspected it had something to do with the 64 bit platform so he made sure to compile for AnyCPU, but it did not work...
Answer: It was the OracleClient fault. The OracleClient for ADO.NET do not work for AnyCPU. You need to use the 64 or 32 bit version.
Linc\EAE used profiles for their queries. Well the profile information is used by our migration tool to generate indexes.
In Java is easy to intercept all SQL statements used by the translated application and analyze them.
To analyse how a query is executed you have to study its explain plan. For go here an excellent guide on EXPLAIN PLAN.
After you read that page, you will find useful the following function, that will shorten the lines that you have to type to see the explain plan:
create OR REPLACE function ShowPlan return sys_refcursor
as
c_test sys_refcursor;
BEGIN
open c_test for select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from
sys.plan_table$ start with id = 0 connect by prior id=parent_id;
return c_test;
END;
SQL>
explain plan for select * from MY_TABLE
SQL> variable rc refcursor
SQL> exec :rc := testfunc()
PL/SQL procedure successfully completed.
SQL> print rc
Operation Object
------------------------------ ------------------------------
SELECT STATEMENT ()
TABLE ACCESS (FULL) MY_TABLE
Microsoft announced that they won’t support the System.Data.OracleClient anymore :(
The message says that it will still be available in .NET 4.0 but “deprecated” and that it wont impact existing applicatoions.
So what are my options?
Well you have to go to a third party. Not now but eventually.
Oracle Data Provider for .NET (ODP.NET) (Oracle states that ODP.NET is Free)
DataDirect ADO.NET Provider for Oracle
dotConnect for Oracle
Migration to ODP.NET
Installation
Microsoft OracleClient to ODP.NET Application Migration: Code Migration
Oracle provide general instructions. In summary you need to:
- Add the references to Oracle.DataAccess
- Change namespaces: System.Data.OracleClient by Oracle.DataAccess.Client; and maybe add “using Oracle.DataAccess.Types”
- Update the connection String (most attributes are the same). Change Integrated Security by "User Id=/", change “Server” attribute to “Data Source” and remove Unicode
- The following is anoying but you have to add after all OracleCommands something like OracleCommand1.BindByName = true;
Deployment
- Oracle Universal Installer (either in normal installation or Silent Install)
- XCopy (I like this :) ) just remember to download the ODAC xcopy version