Hi all, did you never try to use the method ExecuteDataset for Oracle DataObject? for what I read in Oracle forum (and my oracle knowlodge) it should be impossible to use it... I mean there is the method: public static DataSet ExecuteDataset(string connectionString,
CommandType commandType, string commandText) if I pass the connection string, the type storeprocedure, and the packackename.storeproc IT CANNOT WORK!!!!!!!!!!!!!!! I just read this posting http://asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=266984 and it
seem that to call a sp that return a dataset we HAVE to pass an inputoutput parameter of refcursor. Is it right? is there a way to make a storeprocedure in Oracle without passing anyparameter? how is it possible? thanks all for the help!
Well, there are a lot more overloads to ExecuteDataSet, including one where you pass the SPName and an array of objects for the input/output values. I've used these overloads without a problem...
OK, I understood, but I just would like to know if there is anyway to MAKE a SP to get a dataset withouth passing any parameter.... From Microsoft code comments I understand that there is a way (like in SQL 2k) but it seem me impossible there is no way to make
a SP to have back a dataset without passing a ref cursor. Thanks & regards
A oracle procedure can only return a value if the parameter is type out PL/SQL Procedure in Oracle
procedure getempLessThan(pempid in number,curReturn out refCursor) is begin open curReturn for select * from employee where empid<=pempid ; end getempLessThan;
.ExecuteDataset(connection, CommandType.StoredProcedure, "pkg.getempLessThan", New OracleParameter(":EmpID", 3000)) .ExecuteDataset(connection, CommandType.Text, "Select * from employee where empid >= ? and empid <= ?", arParms) Both of thes above statement
work! Regards
I believe that this is an implementation difference in Oracle that is independent of the ExecuteDataset methods. As I understand it, you must use a REF CURSOR as an output parameter to return a record set (in the generic sense) from an Oracle stored procedure.
The OracleHelper.ExecuteDataset method could call any valid stored procedure in Oracle. However, Oracle uses output parameters to expose the record set. Any routine (ADO.NET or otherwise) that calls the stored procedure must pass an output parameter to access
the record set. I used this
article as a reference to create a stored procedure that returned a record set and interface with the stored procedure using ADO.NET. The Nile3.0SimpleSample.exe application referenced in the article uses the OracleHelper class (based on SqlHelper from
the Microsoft Data Access Application Block) to return DataSets from an Oracle stored procedure. Hope that helps.
stelio
Member
10 Points
2 Posts
Oracle DataAccess -- ExecuteDataset wrong?
Sep 29, 2003 02:38 PM|LINK
Stephen Vaki...
Contributor
2540 Points
508 Posts
Re: Oracle DataAccess -- ExecuteDataset wrong?
Sep 29, 2003 06:59 PM|LINK
stelio
Member
10 Points
2 Posts
Re: Oracle DataAccess -- ExecuteDataset wrong?
Sep 30, 2003 07:51 AM|LINK
TinyPond
Member
739 Points
228 Posts
Re: Oracle DataAccess -- ExecuteDataset wrong?
Oct 01, 2003 02:09 PM|LINK
.ExecuteDataset(connection, CommandType.StoredProcedure, "pkg.getempLessThan", New OracleParameter(":EmpID", 3000)) .ExecuteDataset(connection, CommandType.Text, "Select * from employee where empid >= ? and empid <= ?", arParms) Both of thes above statement work! Regards
Weston Binfo...
Member
190 Points
38 Posts
Re: Oracle DataAccess -- ExecuteDataset wrong?
Oct 02, 2003 12:07 AM|LINK
Trason Consulting, Inc.
wbnospam@yahoo.com
(yes, it is a real e-mail address)