DevOps, CI/CD and Automation

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.

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';