I created a .NET stored procedure by following the instructions on this web (used different proc name):
I can deploy it fine into the database but got the following error when I ran it in SQL*Plus Worksheet: ORA-20100: System.AccessViolationException Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at Oracle.DataAccess.Client.OpsCon.OpenUsingExtProcContext(IntPtr ociExtProcContext, IntPtr& opsConCtx, IntPtr& opsErrCtx, OpoConValCtx pOpoConValCtx, OpoConRefCtx& pOpoConRefCtx)* at Oracle.DataAccess.Client.OracleConnection.OpenExtprocConnection() at Oracle.DataAccess.Client.OracleConnection.Open() at OracleTestProject.Class1.GetName() ORA-06512: at "SYS.DBMS_CLR", line 243 ORA-06512: at "COMMPROD10.GETNAME", line 6
I got the following error if I ran it again: ORA-20100: System.AccessViolationException Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at Oracle.DataAccess.Client.OpsSql.ExecuteReader(IntPtr opsConCtx, IntPtr& opsErrCtx, IntPtr& opsSqlCtx, IntPtr& opsDacCtx, IntPtr& opsReaderErrCtx, IntPtr opsSubscrCtx, Int32& isSubscrRegistered, Int32 bchgNTFNExcludeRowidInfo, Int32 bQueryBasedNTFNRegistration, Int64& query_id, OpoSqlValCtx& pOpoSqlValCtx, String pCommandText, OpoDacValCtx*& pOpoDacValCtx, IntPtr pOpoPrmValCtx, String ppOpoPrmRefCtx, OpoMetValCtx*& pOpoMetValCtx, Int32 NoOfParams)* at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader() at OracleTestProject.Class1.GetName() ORA-06512: at "SYS.DBMS_CLR", line 243 ORA-06512: at "COMMPROD10.GETNAME", line 6
The error was gone if I changed the connection string "context connection=true" to regular connection string with specific Data Source;User ID;Password in the .NET stored proc. Does anyone know how to solve it?
FYI, I have installed Oracle Database 10.2.0.1.0 and then upgraded ODE.NET to 10.2.0.2.20 and then installed Oracle Database patch 10.2.0.4 on my machine.
I have also installed the latest ODP.NET 126.96.36.199.20 and Oracle Developer Tools for Visual Studio from the web:
The top most line in my GAC showed both Oracle.Database.Extensions and Oracle.DataAccess have Version= 188.8.131.52.
Thank you so much for the help!
Thank you for the reply!
Yes, the Oracle Database patch 10.2.0.4 contains both ODE and ODP. The database I tried to connect to is on my machine so there was no remote server.
Here is the result when I ran: gacutil /l oracle.database.extensions:
Microsoft (R) .NET Global Assembly Cache Utility. Version 2.0.50727.42
Copyright (c) Microsoft Corporation. All rights reserved.
The Global Assembly Cache contains the following assemblies:
oracle.database.extensions, Version=184.108.40.206, Culture=neutral, PublicKeyToke
oracle.database.extensions, Version=220.127.116.11, Culture=neutral, PublicKeyToken
oracle.database.extensions, Version=18.104.22.168, Culture=neutral, PublicKeyToken
oracle.database.extensions, Version=22.214.171.124, Culture=neutral, PublicKeyToken
Here is the result when I ran: gacutil /l oracle.dataAccess:
The Global Assembly Cache contains the following assemblies:
oracle.dataAccess, Version=126.96.36.199, Culture=neutral, PublicKeyToken=89b483f
oracle.dataAccess, Version=188.8.131.52, Culture=neutral, PublicKeyToken=89b483f4
oracle.dataAccess, Version=184.108.40.206, Culture=neutral, PublicKeyToken=89b483f
oracle.dataAccess, Version=220.127.116.11, Culture=neutral, PublicKeyToken=89b483f4
oracle.dataAccess, Version=18.104.22.168, Culture=neutral, PublicKeyToken=89b483f
I am not sure which version is really used. Maybe I should downgrade my ODP?
Everything seems to be perfect in your config so far. As a last resort can you please check what version of Oracle.DataAccess.dll is loaded by the extproc.exe when you run your .NET stored procedure? Are there two Oracle.DataAccess.dll being loaded in extproc.exe? You may use ProcessExplorer for this.
There was only one Oracle.DataAccess.dll and one Oracle.Database.Extensions.dll loaded in extproc.exe. Here is the result from Process Explorer:
Please redeploy your .NET stored procedure DLL again. This time change the ODP.NET reference to version 22.214.171.124. This ODP.NET 126.96.36.199 must be available in your machine. Please make sure to stop extproc.exe before you re-execute your .NET stored procedure again. If it does not work for you, you may have to ungac policy file for ODP.NET 188.8.131.52 (Policy.2.102.Oracle.DataAccess.dll).
The idea is to use ODP.NET compatible with installed ODE. Since you installed ODT in the same machine with higher version, it is generating .NET stored procedure DLL with ODP.NET 184.108.40.206 and also each previous ODP.NET reference might go to the latest 220.127.116.11 due to publisher policy which might have been installed as part of latest ODT.
You may have to again use ProcessExplorer to make sure that your .NET stored procedure is loading correct ODP.NET version in extproc.exe.
I think I may have messed up my machine.
I stopped extproc.exe and opened my Oracle Project in VS 2008 and ran the Oracle Deployment Wizard for .NET. I didn't find a way to change the ODP.NET reference version. The last screen showed the Oracle.DataAccess.dll reference path was C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x. So I went to that path and made a copy of Oracle.DataAccess.dll and then copied C:\oracle\product\10.2.0\db_1\odp.net\bin\2.x\Oracle.DataAccess.dll (had version 18.104.22.168) to this folder. Then finished the Wizard. I got the same error as before. The Process Explorer showed that extproc.exe still loaded ODP.NET 22.214.171.124.
Here is what I may have done wrong. I thought redeploy didn't work. So I ran the following in GAC:
gacutil /u oracle.dataAccess
gacutil /i C:\oracle\product\10.2.0\db_1\odp.net\bin\2.x\Oracle.DataAccess.dll
Now I am getting this error when I ran my proc:
ORA-20100: AppDomain could not be created for the specified security level
ORA-06512: at "SYS.DBMS_CLR", line 152
I saw some discussion in another thread and you pointed out that this error would usually come if there is a problem in corresponding ODP.NET installation in server machine. Is there an easy way to get my GAC back to normal? I hate to re-install everything (I mean Oracle) on my machine again!
I don't understand why Oracle couldn't make later version of ODP.NET to work with previous version of ODE. Besides, I don't see version 11 ODE available on their website. I tried to install the same version of ODP.NET and ODE but Oracle just wouldn't make this easy!
Thanks so much!
One more thing I forgot to mention. When I had the AppDomain error, the Process Explorer showed that extproc.exe didn't even load Oracle.DataAccess.dll. Maybe it couldn't find the dll due to GAC problem?
You need to change ODP.NET reference to (C:\oracle\product\10.2.0\db_1\odp.net\bin\2.x\Oracle.DataAccess.dll) in the VS.NET project before running the deployment wizard for .NET stored procedure. Basically your .NET stored procedure DLL should reference ODP.NET of 10gR2 as your ODE is 10gR2.
Please restore 11gR1 ODP.NET back in C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x then GAC this ODP.NET again as you have ungac'ed it.
Ungac Policy.2.102.Oracle.DataAccess.dll and try redeploy and re-run of .NET stored procedure again.
The fact that you have both ODE and ODT in the same machine and ODT has higher version than ODE is making it complicated. ODE 11gR1 is available for download but you need to have 11gR1 db to use ODE 11gR1.
Hope this will help resolving your problem.
Thank you for your patience!
I have changed ODP.NET reference to 10.2 in VS project and restored 11gR1 ODP.NET file. Then I ran the following in GAC:
gacutil /u oracle.dataAccess
gacutil /i C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll
Then I redeployed the Oracle Project and I can see it now references C:\oracle\product\10.2.0\db_1\odp.net\bin\2.x\Oracle.DataAccess.dll in the Wizard.
I am now getting the original System.AccessViolationException error when I ran the .NET proc and the Process Explorer showed that extproc.exe still loaded ODP.NET 126.96.36.199.
On a side note, I remember now why I installed the version 11g ODP.NET. I wanted to install Oracle Developer Tools that works on VS 2008 and it seemed that only version 11g one would fit. When I installed the ODTwithODAC1110621.zip from Oracle's web - http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html, I didn't find a way to only install ODT without getting ODP.NET.
Any other suggestions? I really appreciate your help!
It seems you did not ungac "policy dll".
1. Please gac 10gr2 ODP.NET available in C:\oracle\product\10.2.0\db_1\odp.net\bin\2.x\Oracle.DataAccess.dll
(Do not do gacutil /u oracle.dataAccess before gac'ing this DLL since we want both ODP 11g and 10g in the gac)
2. ungac Policy dll by doing - gacutil /u Policy.2.102.Oracle.DataAccess
And try running your .NET SP after restarting extproc.
I ran with similar problem in the uat server while opening a connection to database.
After deploying the application from dev server to uat i was getting the weird exception "System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt." and was unable to identify the root cause for more than a day.
OracleConnection conn = new OracleConnection(connstring);
oracleCommand = conn.CreateCommand();
Fix: In the environment variables the path variable was set to wrong version of oracle client which was causing the error.After updating it everything went successfull.