This content has been marked as final. Show 6 replies
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.