| Oracle and ADO [message #76272] |
Mon, 07 January 2002 06:50  |
Eric Workman
Messages: 3
Registered: November 2001
|
Junior Member |
|
|
Hello-
I am new to Oracle and am trying to call stored procedures migrated over from SQL Server through ADO. I have two questions.
1) How do you call a stored FUNCTION in Oracle from ADO.
2) How do I handle stored Procedures of Functions that return multiple recordsets.
Thanks,
Eric Workman
|
|
|
|
|
|
| Re: Oracle and ADO [message #76481 is a reply to message #76272] |
Tue, 20 August 2002 08:16  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
1) You call it the same as a Procedure with one difference. The first Parameter needs to have a direction of adParamReturnValue. Make sure its the correct type for the data type returned by your function. Say your calling
---------------------------------
Function get(primary_key integer) return varchar2
is
Begin
return "data-data-data-data";
end get;
-----------------------------------
Define your two parameters using the parameter
create and append metods of the command object. Like the partial code below.
-----------------------------------------------
parameter name, datatype,direction, size, value
----------------------------------------------
"", adVarChar, adParamReturnValue, 32000, ""
"primary_key",adInteger,adParamInput,4,2
Note: I haven't successfully returned an array or a recordset to date this way. Just varchars,
integers etc.
2) I don't use this myself but you can get recordsets by using the microsoft oracle driver.
|
|
|
|
| Re: Oracle and ADO [message #76881 is a reply to message #76389] |
Fri, 06 February 2004 12:23 |
John Sheehan
Messages: 1
Registered: February 2004
|
Junior Member |
|
|
Say you have an Oracle function oraf(p_num in number) that returns a double. In C#, if "cnn" is your ADODB connection to Oracle, the code is:
OleDBCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "oraf";
OleDBParameter pm = new OleDBParameter("retvalue", System.Data.OleDb.OleDbType.Double);
cmd.Parameters.Add(pm);
OleDBParameter pm = new OleDBParameter("p_num", System.Data.OleDb.OleDbType.Integer);
cmd.Parameters.Add(pm);
cmd.Parameters[["p_num"]].Value = 123;
cmd.ExecuteNonQuery();
double result =
(double) cmd.Parameters[["retvalue"]].Value;
-------------
The return value *must* be the first parameter in the command.
--John Sheehan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
