Skip to Main Content
  • Questions
  • How could I call dll from PL/SQL or Oracle Developer

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Waleed.

Asked: April 08, 2003 - 7:42 am UTC

Answered by: Tom Kyte - Last updated: May 16, 2005 - 8:03 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Sir

How could I call from PL/SQL a dll written by VB.NET?

Thanks

and we said...

So, they are calling simple DLL's .net -- cool. Take a 15 year old technology and call it "internet" and it is.


</code> http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76939/adg11rtn.htm#1656 <code>

described external routines. Yes, PLSQL can invoked a good old fashioned DLL.

and you rated our response

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

You can also use ORA_FFI

April 08, 2003 - 2:19 pm UTC

Reviewer: graeme king from new york

In forms (although not sure about 9i forms) you can call a dll using the built-in package ORA_FFI....see the help for more details.

graeme

Does Oracle COM Automation support VB.NET?

April 09, 2003 - 1:55 am UTC

Reviewer: Waleed from Jordan

Can I call dll writen in VB.NET from pl/sql using Oracle COM Automation ?

I will appreciate if you provide me an example of how to call VB.NET dll from pl/sql by using any of the technologies.


Tom Kyte

Followup  

April 09, 2003 - 8:49 am UTC

did you, umm, read the documentation I pointed you to?

it shows how to call a dll from plsql. You simply write a wrapper.

Hey, here is an example:

procedure pass( p_in in varchar2, p_out out varchar2 )
as
language C name "pass_str" library demoPassing
with context parameters
( CONTEXT,
p_in STRING, p_in INDICATOR short,
p_out STRING, p_out INDICATOR short, p_out MAXLEN int );

that passes a string to and fro a C dll (or .so or .sl whatever your OS supports). The C prototype that would map to would be:

-- void pass_str
-- (
-- OCIExtProcContext *, /* 1 : With-Context */
-- char *, /* 2 : P_IN */
-- short , /* 3 : P_IN (Indicator) */
-- char *, /* 4 : P_OUT */
-- short *, /* 5 : P_OUT (Indicator) */
-- int * /* 6 : P_OUT (Maxlen) */
-- );


Now, what you need to do is figure out what the C prototype for this DLL is, create the appropriate mapping, and away you go. Given that I have not a single machine capable of doing "VB" -- thats about as far as I'll be going here...



Only C routines

April 10, 2003 - 3:33 am UTC

Reviewer: waleed from Jordan

I would like to thank you very much, but Oracle said in Oracle8i Application Developer's Guide - Fundamentals Chapter 10 "Only C routines and routines callable from C code are supported", then I can not use the external procedures to call VB.NET dll, what do you say?

Thanks

Tom Kyte

Followup  

April 10, 2003 - 8:02 am UTC

Routines in the DLL that use the C calling format.

Once it is compiled into object code -- VB looks just like C you know.

Connect from VB.net to Oracle by DLL

August 07, 2003 - 5:17 am UTC

Reviewer: ThemVV from USA

I want connect from VB.net to Oracle, I used
myConnection = New OracleClient.OracleConnection("Password=QLAC_OWNER;User ID=QLAC_OWNER; Data Source=ANCHI;Persist Security Info=False")

or uses Oledb : provider="OraOLEDB.Oracle.1"
but all of them use Data Source = "TNSNAME"
I must give TNSName to use net8 but infact I don't want setup client Oracle or net8 client (because version, in my project, the customer used older version: 6i,....).
Can you tell me the useful DLL use connect from VB.net to Unicode ORacle 9i without using net8 (by IP and SID like JDBC)


Tom Kyte

Followup  

August 09, 2003 - 4:24 pm UTC

It would be called "jdbc"

If you use ODBC, you need to have the client installed (and there was no "6i" net client - there was a forms version called 6i but that was based on the 8i client libraries.

Please Help

May 14, 2005 - 6:09 pm UTC

Reviewer: Loai from Jordan

im realy sick of looking here and there for a solution for calling procedures written in VB or VB.NET language, can oracle support such a thing or not, cause tom, i don't think it works with any thing else than C, C++ or Java. Help pleassssssseeeeeeeeeeeeeeeeeee
this error keeps showing up (ORA-00478)

Tom Kyte

Followup  

May 14, 2005 - 6:59 pm UTC

well, I'm not a vb or vb.nyet programmer so you shouldn't expect to see tons of examples.

I know plsql can call stored procedures too by the way :)

You are really getting:

[tkyte@localhost tkyte]$ oerr ora 478
00478, 00000, "SMON process terminated due to error %s"
// *Cause: SMON was unable to service the requests due to error in
// cleanup of resources
// *Action: Warm start instance


????? are you sure that is it?


I just googled

calling stored procedure from vb.net oracle


and it looked very promising.

VB.NET Procedures

May 14, 2005 - 8:08 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Hi Loai,

Are you are looking to use external procedures in .NET similar to external procedures that are written in C? Oracle currently supports invoking COM automation code using the COM automation feature. In addition, the Oracle Database 10g Release 2 will support access to .NET stored procedures via the DBMS_CLR PL/SQL package.

I would provide a link to the COM automation feature documentation on OTN but the "Service Is Temporarily Unavailable" at this time.

- Mark

It's taking great time to do that

May 15, 2005 - 1:14 am UTC

Reviewer: loai from Jordan

thanks tome for your reply, the problem exactly is that im working on both 8i, and 10g, im trying to call a VB written dll to checkup somthing in another DB which is access, i know it's stupid, but this what i'm asked to do, it took me one whole day of my life to configure both Tnsnames.ora and listener.ora files, and when i thought that i solved it at last, another problem came wich the operation is not performed completly and it keeps giving the error ORA-00487, and here's the completly what i done
the dll file in VB.Net:
Function SUMINT(ByVal Num1 As Integer, ByVal Num2 As Integer) As Integer
        Dim Result As Integer
        If Num1 = Num2 Then
            Result = 1
        Else
            Result = 0
        End If
        SUMINT = Result
    End Function


the pl/sql that i wrote:
CREATE OR REPLACE LIBRARY dll_test
AS
   'C:\Here\TestDll.dll'

CREATE OR REPLACE PACKAGE dll_test_func
AS
   FUNCTION numeric_sum
     (x in pls_integer, y in pls_integer)
   RETURN PLS_INTEGER;
END dll_test_func;

CREATE OR REPLACE PACKAGE body dll_test_func
AS
   FUNCTION numeric_sum
     (x in pls_integer, y in pls_integer)
   RETURN PLS_INTEGER
   IS EXTERNAL
      LIBRARY dll_tes -- my library (defined previously)
      NAME "SUMINT"   -- name of function in kernel32.dll
      LANGUAGE C --VISUAL BASIC ??                 -- external routine is written in C
      CALLING STANDARD PASCAL <-- what is this? should i
                               --use it
      PARAMETERS                 -- map PL/SQL to C parameters by
                                 -- position
         ( x BY REFERENCE int, y BY REFERENCE int, RETURN int); 
END dll_test_func;


SQL> declare
  2  XX pls_integer;
  3  begin
  4  XX := dll_test.numeric_sum(1,1);
  5  --CCC:=33
  6  end;
  7  /

the Error i had:
CCC := dll_test.numeric_sum(1,1);
                *
ERROR at line 4:
ORA-06550: line 4, column 17:
PLS-00487: Invalid reference to variable '<expression>'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

i don't know what is the problem tom, i changed alot, and red alot papers, books and files, and nothing untill now, it has been a week, and i need a real solution for both 10g(Com automation??) and 8i, please help and thanks alot, we realy do appreciate your effort helping us specialy for technologies that you may not use such as .NET, thanks 

Tom Kyte

Followup  

May 15, 2005 - 9:34 am UTC

well, the name of the package is dll_test_FUNC, not dll_test -- that is the error in the anonymous block.

and the directory object you created is DLL_TEST not DLL_TES


but I don't know how VB (it is just basic after all, ".net" hmm) maps its inputs. does it want a point, or by value. are they C calling conventions, or Pascal calling conventions.


PL/SQL procedure DLL_TEST_FUNC.NUMERIC_SUM
Maps to a C function with the following prototype:

int SUMINT
(
int *, /* 1 : X */
int * /* 2 : Y */
);


You'll have to verify that is what VB is expecting beyond these typos you have to fix.

Thanks Mark

May 15, 2005 - 7:41 am UTC

Reviewer: Loai from Jordan

thank you mark for help, i`ll try to check out the package you refered to, thanks again.
and tom, i'm waiting your openion in this issue, thanks again all.

Tom Kyte

Followup  

May 15, 2005 - 9:37 am UTC

fix your typos and see what you see....


Thanks Tom

May 15, 2005 - 10:12 am UTC

Reviewer: Loai from Jordan

i've been awake for two days, i didn't sleep well, I'll try hard to consetrate next time, any way thanks a lot tom for your big assistant, and i'll try your solution, thanks again.

Maping?!!

May 15, 2005 - 10:45 am UTC

Reviewer: Loai from Jordan

Tom i tried to map the variables as you showed me before, but i don't know where? it's in the package body??

Tom Kyte

Followup  

May 15, 2005 - 11:17 am UTC

I don't know what you mean.


You map the variables by creating the right function prototype for your implementation. Your example had:

FUNCTION numeric_sum
(x in pls_integer, y in pls_integer)
RETURN PLS_INTEGER
IS EXTERNAL
...
PARAMETERS ( x BY REFERENCE int, y BY REFERENCE int, RETURN int);


which maps to a C routine that would be coded like this:

int SUMINT
(
int *, /* 1 : X */
int * /* 2 : Y */
);


Now, if that is what the VB routine is expecting -- two pointers (pass by reference), and returns a C int, thats great

If not, I don't know what the mapping is, for I know nothing about basic. I don't know if your basic routine wants things passed by reference or by value. If it reeturns a 2 byte int or a 4 byte long. Etc....................




Another solution

May 15, 2005 - 11:48 am UTC

Reviewer: Loai from Jordan

thanks tom, but here what i'll try to do, I'll generate a C dll functions, and try to call the VB dll file from the C dll file, I don't know what to do, may be it's stupid, but that what i have in mind for now, what do you think?


Tom Kyte

Followup  

May 15, 2005 - 12:10 pm UTC

if you do it in C, we'll at least know what the plsql function needs to map to


It sounds like an excellent idea.

This can be done without any code

May 16, 2005 - 4:07 am UTC

Reviewer: Tom

Tom,

Suprised you didn't mention that since the poster wants to "checkup some data in an access database" then using a VB.NET dll to do it, is possibly the most complex way of doing it there is. In terms of simplicity would the following not be better [and more OS portable :-) ]

1. heterogeneous connectivity - query access directly
2. Java stored procedure - use jdbc direct to access

Tom Kyte

Followup  

May 16, 2005 - 8:02 am UTC

either of those would work -- yes.

I assumed the existing VB code needed to be called, it was performing some logic


but you are 100% correct, if we just need to query an access table, the heterogenous services would take very little setup.