You can also use ORA_FFI
graeme king, April 08, 2003 - 2:19 pm UTC
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?
Waleed, April 09, 2003 - 1:55 am UTC
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.
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
waleed, April 10, 2003 - 3:33 am UTC
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
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
ThemVV, August 07, 2003 - 5:17 am UTC
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)
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
Loai, May 14, 2005 - 6:09 pm UTC
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)
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
Mark A. Williams, May 14, 2005 - 8:08 pm UTC
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
loai, May 15, 2005 - 1:14 am UTC
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
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
Loai, May 15, 2005 - 7:41 am UTC
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.
May 15, 2005 - 9:37 am UTC
fix your typos and see what you see....
Thanks Tom
Loai, May 15, 2005 - 10:12 am UTC
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?!!
Loai, May 15, 2005 - 10:45 am UTC
Tom i tried to map the variables as you showed me before, but i don't know where? it's in the package body??
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
Loai, May 15, 2005 - 11:48 am UTC
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?
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
Tom, May 16, 2005 - 4:07 am UTC
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
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.