Home » Infrastructure » Windows » How can I make an existing VB.NET DLL's functions available to stored procedures? (Oracle 11g R2)
How can I make an existing VB.NET DLL's functions available to stored procedures? [message #625585] Fri, 10 October 2014 11:56 Go to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
I have a generic string manipulation function developed as a class as follows;

Public Class MyClass 
     Public Function MyFunction(ByVal inString As String) As String 
          Dim MyString as String = inString & " Some Stuff" 
          Return MyString 
     End Function 
End Class


The real function does real stuff, but it is all string manipulation, no DB, file or screen access.

Is there an easy way to make this function callable from a procedure within a package within an Oracle DB?

I have installed Oracle DB Extensions for .NET on the DB server.

I am hoping to avoid installing anything on the computer where I develop my VB.NET code.

I have created external packages using a c library (c++ DLL) before, but I was under the impression that .NET is different. Everything I find when searching seems to talk about some form of extension to VS that allows an "easy" deploy straight from .NET.

I will eventually need to deploy this on multiple DBs and want a manual process that I can just run, and not connect to each DB from my .NET development computer.

I have an existing C++ library I call from PL/SQL. When I define the package body it looks like;
create or replace 
PACKAGE BODY  "MY_PACKAGE" AS FUNCTION MY_FUNCTION (VAR1  in varchar2, VAR2 in varchar2, VAR3 in varchar2)RETURN PLS_INTEGER 
IS 
     EXTERNAL 
     LIBRARY DLL_MY_LIBRARY 
     NAME "MY_FUNCTION" 
     LANGUAGE C 
     PARAMETERS (VAR1 string, VAR2 string, VAR3 string); 
END MY_PACKAGE;


What does the definition for a VB.NET function within a class look like? Do I need dot notation like;

create or replace 
PACKAGE BODY  "MY_PACKAGE" AS FUNCTION MY_CLASS.MY_FUNCTION (VAR1  in varchar2, VAR2 in varchar2, VAR3 in varchar2)RETURN PLS_INTEGER 
IS 
     EXTERNAL 
     LIBRARY DLL_MY_LIBRARY 
     NAME "MY_CLASS.MY_FUNCTION" 
     LANGUAGE VB.NET 
     PARAMETERS (VAR1 string, VAR2 string, VAR3 string); 
END MY_PACKAGE;


What should the language be?

What would be useful is documentation that shows how to manually add an existing .NET DLL to a database so that it can be accessed by PL/SQL. If this is available, please point me to it.

All of the documentation that I have found just shows how to install and use the VS add on.
Re: How can I make an existing VB.NET DLL's functions available to stored procedures? [message #625587 is a reply to message #625585] Fri, 10 October 2014 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Better convert your VB function into a PL/SQL one.
Using external procedure/DLL/program/... is a security hole and a bad idea.

[Updated on: Fri, 10 October 2014 12:34]

Report message to a moderator

Re: How can I make an existing VB.NET DLL's functions available to stored procedures? [message #625588 is a reply to message #625587] Fri, 10 October 2014 12:56 Go to previous messageGo to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
Th function relies heavily on regex.match. My original idea was to rewrite it in PL/SQL, but gave up after about 5 hours of coding.

If you are offering to rewrite the code for a reasonable fee, I would be happy to entertain the idea. It is an opensource VB.NET routine available here http://www.analysisandsolutions.com/software/addr/addr.htm, I have added a small bit of code to it.
Re: How can I make an existing VB.NET DLL's functions available to stored procedures? [message #625589 is a reply to message #625588] Fri, 10 October 2014 13:20 Go to previous message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I will have a look, at least at the PHP version I better know, but for the moment I have many things to do and so less time.

Previous Topic: trasportable tablespace error during export
Next Topic: Has anyone actually created a .NET stored procedure?
Goto Forum:
  


Current Time: Tue Nov 30 06:35:44 CST 2021