Getting a full dataset from Oracle through vb.net / ADO.net?

[jobs]

I have similar code (a procedure and function) working perfectly with
a sql server data source. I'm trying to do the same things with
Oracle, but have run into the problems.


I'm not sure if producing a cursor is my only and best option.


PROCEDURE sp_Get_Order_History(v_ani IN number,

output_cursor out PK_CURSORES.generic_cursor) AS

BEGIN


open output_cursor for

select o.account_number, p.phone, o.amount, o.name, o.init_date

from orders o

join users_phones p on p.account_number = o.account_number

where p.phone = v_ani

order by Init_date desc;

end sp_Get_Order_History;







in vb.net:







Function GetOrderHistory(ByVal Phone As String) As DataSet

If p_cnn.State = ConnectionState.Closed Then

p_cnn.Open()

End If



Dim cmd As New OracleCommand

With cmd

.CommandType = CommandType.StoredProcedure

.CommandText = "PK_RETAILER.sp_Get_Order_History"

.CommandTimeout = 0

.Connection = p_cnn

.Parameters.Add(New OracleParameter("v_ani",
OracleType.VarChar)).Value = Phone

.Parameters.Add(New OracleParameter("output_cursor",
OracleType.Cursor)).Direction = ParameterDirection.Output

End With



Dim adpt As New
OracleDataAdapter(cmd.Parameters("output_cursor").Value)

Dim ds As New DataSet

adpt.Fill(ds, "orders")

Return ds



cmd.Dispose()

p_cnn.Close()

End Function







In my markup:



<asp:ObjectDataSource ID="DSOrders" runat="server"
SelectMethod="GetOrderHistory"

TypeName="xxx.xxxx">

<SelectParameters>

<asp:ControlParameter
ControlID="CustomerPhoneTextBox" PropertyName="Text" Name="Phone"

Type="String" DefaultValue=""/>

</SelectParameters>

</asp:ObjectDataSource>





I get this error:



The SelectCommand property has not been initialized before calling
'Fill'.

Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.



Exception Details: System.InvalidOperationException: The SelectCommand
property has not been initialized before calling 'Fill'.



Source Error:



Line 61: Dim adpt As New
OracleDataAdapter(cmd.Parameters("output_cursor").Value)

Line 62: Dim ds As New DataSet

Line 63: adpt.Fill(ds, "orders")

Line 64: Return ds

Line 65:







In sql server I have procedure that produces a Dataset,not a cursor.
I'm likely needing to loop through the cursor right?



Thanks for any help or information.