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
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 #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
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 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);
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
Previous Topic: Oracle 9i Application Installation
Next Topic: Other Products like iAS 9i
Goto Forum:
  


Current Time: Wed Sep 23 11:50:09 CDT 2020