Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

.NET SP error ORAXE 11g on WIN10

ChrisFisherJul 15 2021

HI,
I'm trying to recreate old data flow in our test environment - originally with Oracle XE11g running on win XP(I know it's more than mature now).
Code contains one .NET stored procedure. It works when I have it on older OS (XP/Win7), but after installing it on Win10 running that function fails with following error.
select myuser.getValue from dual
*
ERROR at line 1:
ORA-20100: ODE-00005: Could not create type:
Oracle.Database.Extensions.OracleAppDomainFactory, Oracle.Database.Extensions,
Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: at "MYUSER.GETVALUE", line 6

I followed the same procedure on all systems
clean install XE DB
copy dll ( mscorlib.dll spproj.dll System.Management.dll and my library to D:\oraclexe\app\oracle\product\11.2.0\server\bin\CLR)
create library object, function and grants for them.
grant execute on dbms_clr, dbms_clrparamtable, dbms_clrtype.
call the function
Now I get error provided above.
these are .net frameworks installed:
Name Version
----------- -------
v2.0.50727 2.0.50727.4927
v3.0 3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation 3.0.6920.4902
v3.5 3.5.30729.4926
Client 4.8.04084
Full 4.8.04084
Client 4.0.0.0

I tried 32 and 64 bits windows and XE. I need to recreate for test and upgrade cycle.
Any hints 're welcome.

Comments

Alex.UA

Check this out ORA-20100: Could not create type using ODE.NET (Doc ID 394682.1)

Try to reproduce step by step.

using System.Text.RegularExpressions;
namespace CLR2
{
   public class Class1
   {
       public static string StoredProcedure1(string str, string pattern)
       {
           return Regex.Replace(str, pattern, m => m.ToString().ToUpper());
       }
   }
}

After building assembly in Visual Studio (VS) let's check if function works fine

PS C:\Users\yo> Add-Type -Path c:\clr2.dll
PS C:\Users\yo> [CLR2.Class1]::StoredProcedure1("hello world", "l+")
heLLo worLd

Finally let's test in Oracle

create or replace function storedprocedure1(str varchar2, pattern varchar2) return varchar2 authid current_user is
begin
 declare
 begin
   dbms_clr.clearparameters;
   dbms_clr.setvarcharparam(str, dbms_clr.inparam, dbms_clr.system_string);
   dbms_clr.setvarcharparam(pattern, dbms_clr.inparam, dbms_clr.system_string);
   return dbms_clr.executevarcharfunction('CLR2', 'CLR2.Class1', 'StoredProcedure1', dbms_clr.unsafe, 'VSVER=2005;');
 end;
end;
/
Function created.

select storedprocedure1('hello world', 'l+') x from dual;
select storedprocedure1('hello world', 'l+') x from dual
      *
ERROR at line 1:
ORA-20100: ODE-00005: Could not create type:
Oracle.Database.Extensions.OracleAppDomainFactory, Oracle.Database.Extensions,
Version=2.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: at "SYS.DBMS_CLR", line 42
ORA-06512: at "SYS.DBMS_CLR", line 236
ORA-06512: at "SYS.STOREDPROCEDURE1", line 8

create or replace function storedprocedure1(str varchar2, pattern varchar2) return varchar2 authid current_user is
begin
 declare
 begin
   dbms_clr.clearparameters;
   dbms_clr.setvarcharparam(str, dbms_clr.inparam, dbms_clr.system_string);
   dbms_clr.setvarcharparam(pattern, dbms_clr.inparam, dbms_clr.system_string);
   return dbms_clr.executevarcharfunction('CLR2', 'CLR2.Class1', 'StoredProcedure1', dbms_clr.safe, 'VSVER=4.0;');
 end;
end;
/
Function created.

select storedprocedure1('hello world', 'l+') x from dual;
select storedprocedure1('hello world', 'l+') x from dual
      *
ERROR at line 1:
ORA-20100: ODE-00006: AppDomain could not be created for the specified security
level
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: at "SYS.DBMS_CLR", line 42
ORA-06512: at "SYS.DBMS_CLR", line 236
ORA-06512: at "SYS.STOREDPROCEDURE1", line 8

create or replace function storedprocedure1(str varchar2, pattern varchar2) return varchar2 authid current_user is
begin
 declare
 begin
   dbms_clr.clearparameters;
   dbms_clr.setvarcharparam(str, dbms_clr.inparam, dbms_clr.system_string);
   dbms_clr.setvarcharparam(pattern, dbms_clr.inparam, dbms_clr.system_string);
   return dbms_clr.executevarcharfunction('CLR2', 'CLR2.Class1', 'StoredProcedure1', dbms_clr.unsafe, 'VSVER=4.0;');
 end;
end;
/
Function created.

select storedprocedure1('hello world', 'l+') x from dual;
X
--------------------------------------------------------------------------------
heLLo worLd

To cut long story short - if you use wrapped DDL scripts generated by VS they may contain incompatible version in the parameter DBMSCLRINFO to the function EXECUTEVARCHARFUNCTION (or similar one). I assume that version is derived from .net framework version specified in the solution properties in the VS.

PS. Also pay attention to the security level.

PPS. Oracle Database Extensions for .NET is deprecated in Oracle Database 21c.

Alex.UA

Of course, if you do this manually (without deployment feature in VS) you also need to create a library

create library clr2 as '$ORACLE_HOME\bin\clr\clr2.dll';
1 - 2

Post Details

Added on Jul 15 2021
2 comments
410 views