I'm using Oracle External Procedures with C# as shown here:
So I've created Managed C++ wrapper ot the C#'s assembly and exposed the reqired methods as C++ functions.
The problem is that when I call the C++ functions within a PL-SQL query, the C#'s assembly that is
referenced in the C++ dll cannot be found. I tried referencing the C++ dll into a VC++ 6.0 project and
everything works fine: the C#'s code has been executed. After that I added the C#'s assembly to the GAC
and then I achieved the desired effect: the C#'s code was executed from a PL-SQL Query. But after removing
the dll from the GAC and restarting the Oracle Listener thing's went back to "normal" - C#'s assembly not found.
The process loading and executing the dll withing a PL_SQL query is extproc.exe which is running as System.
Finally my question:
Is there a way to call an oracle external procedure without adding the wrapped assembly to the GAC?
Maybe it's a matter of right oracle listener configuration or VC++ reference.
Environment: Visual Studio 2005 Team Suite and Oracle 10g.
I probably do not have an answer to your question however I think if your requirement is just to execute .NET code as a stored procedure or a stored function and also you want to access database from the .NET code then you may use "Oracle Database Extension for .NET" to write these .NET stored procedures/functions in C#/VB.NET/Managed C++.
Check - http://www.oracle.com/technology/tech/dotnet/ode/index.html (Click on "View a demo" for demonstration).
Also check http://www.oracle.com/technology/obe/net11gobe/stfunc/vs2.htm for a walkthrough
Thank you for your reply skj.
But my intention is to "fire an event" from the oracle db based on some kind of db logic (db is the initiator). And actually the .net code is a part of a server that will notify the connected clients. I don't like the db polling option or database change notification, because the event will be fired quite often.
I am not sure if I correctly understood it. Since a .NET Stored Procedure is like a PL/SQL procedure or a C-callspec, I was wondering if your triggering logic could call the PL/SQL wrapper of a .NET Stored Procedure which in turn will execute code on the database server machine in extproc process and this .NET code may be used to notify your clients.
In fact I have the following scenario:
Multiple servers write into an oracle db. Based on some db logic (after some table fields reach some values) the db must notify one specific server on which the clients are connected. Server is written on C# and so I'm using Oracle External Procedure to make this call. But because the dll that can be called from a PL-SQL query must only export C style or Java function I've created a managed C++ wrapper which will call the actual C# assembly. And this scenario is possible but for some reason the extproc which is managing the whole process of the dll execution doesn't load the C# assembly if it is not in the GAC.
(PL-SQL Query -> C++ Wrapper dll -> C# Assembly) extproc loads only the C++ wrapper dll
Perfect. Extproc can execute a .NET Stored Procedure which has a PL/SQL interface simlar to a C-Callspec hence you don't need to write a Managed C++ wrapper to host your .NET code in extproc. It can be something like -
(Your PL/SQL Trigger/Query -> PL-SQL Wrapper for .NET Stored Procedure -> Your .NET Function -> Your .NET Server Code in some other C# Assembly)
In fact, your .NET Server Code can be directly hosted in extproc without having any other layer if you just expose a public static function and deploy that function from "Oracle Developer Tool" as a .NET Stored Procedure. The assembly will automatically copied to the database server and the public static method will server as an entry point into your .NET server dll.
Thank you very much for the offered solution.
I found my silly mistake. I just had to copy my C# assembly
to the %ORACLE_HOME%bin directory. Now my assembly is referenced and working. I haven't worked with "Oracle Developer Tool" but it sounds great and I'll do some research on it.