Skip to Main Content

SQL Developer

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!

Show script output in Query Result tab

j.varianAug 5 2022

I have a stored script.
I run the script and it executes and shows in the "Script Output" tab. Thats ok.
I take the query out of it and run that, It displays in the "Query Result" tab. Thats ok too.
What I want, is to be able to execute the stored script, and to get its results in the "Query Tab", I want this as I can copy certain columns easily from the resultset.

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 Aug 5 2022
0 comments
305 views