Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » Oracle and ADO
Oracle and ADO [message #76272] Mon, 07 January 2002 06:50 Go to next message
Eric Workman
Messages: 3
Registered: November 2001
Junior Member
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.


Eric Workman
Re: Oracle and ADO [message #76389 is a reply to message #76272] Thu, 09 May 2002 04:57 Go to previous messageGo to next message
Steve Ferry
Messages: 1
Registered: May 2002
Junior Member
Has anyone replied to this. I can get procedures to work no problem but functions do not appear to work at all.
Re: Oracle and ADO [message #76481 is a reply to message #76272] Tue, 20 August 2002 08:16 Go to previous messageGo to next message
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
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, ""


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 Go to previous message
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);
OleDBParameter pm = new OleDBParameter("p_num", System.Data.OleDb.OleDbType.Integer);
cmd.Parameters[["p_num"]].Value = 123;
double result =
(double) cmd.Parameters[["retvalue"]].Value;
The return value *must* be the first parameter in the command.

--John Sheehan
Previous Topic: Oracle 9i Application Installation
Next Topic: Other Products like iAS 9i
Goto Forum:

Current Time: Sun Mar 03 03:06:51 CST 2024