asked on

Return Ref Cursor to dataset from oracle stored procedure

I have the following stored procedure in oracle: -

[CODE]CREATE OR REPLACE PACKAGE pkg_machines
AS
TYPE rc_machines IS REF CURSOR;
END;
/

CREATE OR REPLACE PROCEDURE sp_select_machines
      (i_order_no       IN       tbl_orders.order_no%type,
       i_profile_no       IN       tbl_profiles.profile_no%type,
       i_start_date       IN       date,
       i_end_date       IN       date,
       c_machines OUT pkg_machines.rc_machines )
IS
BEGIN

      OPEN c_machines FOR
     SELECT DISTINCT
                              mac.machine_id,
                              ord.order_no,
                              prof.profile_no,
                              ord.picked_date - 2 AS xfactory,
                              ord.picked_date - 4 AS deadline_to_online,
                              mac.machine_status_id,
                              ROUND(ord.picked_date - 2 - sysdate, 0) AS variance,
                              mac.serial_no,
                              ord.postcode,
                              ord.order_status,
                              ord.client_name,
                              mac.date_ordered
            FROM tbl_machines mac, tbl_orders ord, tbl_profiles prof
            WHERE ord.order_id = mac.order_id
            AND prof.profile_id = mac.profile_id
            AND ord.order_no LIKE '%' || i_order_no || '%'
            AND prof.profile_no LIKE '%' || i_profile_no || '%'
            AND (ord.picked_date - 2) BETWEEN TO_DATE(i_start_date) AND TO_DATE(i_end_date);

      END;
/[/CODE]

I am trying to call this from VB.net into a dataset using the following function: -
[CODE]'populate machines table
Public Function addScheduleToBuildData(ByVal I_ORDER_NO As String _
, ByVal I_PROFILE_NO As String _
, ByVal I_START_DATE As Date _
, ByVal I_END_DATE As Date)

Dim ds As New DataSet()
With dbAdaptr
' create the command for the stored procedure
Dim cmd = New OracleCommand()
cmd.Connection = cn
cmd.CommandText = "sp_select_machines"
cmd.CommandType = CommandType.StoredProcedure
' add the parameters for the stored procedure including the REF CURSOR
' to retrieve the result set
cmd.Parameters.Add("i_order_no", OracleType.VarChar).Value = I_ORDER_NO
cmd.Parameters.Add("i_profile_no", OracleType.VarChar).Value = I_PROFILE_NO
cmd.Parameters.Add("i_start_date", OracleType.DateTime).Value = I_START_DATE
cmd.Parameters.Add("i_end_date", OracleType.DateTime).Value = I_END_DATE
cmd.Parameters.Add("c_machines", OracleType.Cursor).Direction = ParameterDirection.Output

.TableMappings.Add("Table", "machines")
' use dataadpater to fill the DataSet
.Fill(ds)
.Dispose()

' output the results.
Console.WriteLine(ds.Tables(0).Rows.Count)
End With
Return ds
End Function[/CODE]

But i am getting the following error on the line ".fill(ds)" :-
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

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

the procedure executes fine within oracle, any help much appreciated.

Cheers,

Dan

ASKER CERTIFIED SOLUTION

imperial_p79

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

danny__t

ASKER

So simple!

Fantastic thanks

mattfox77

I did It!

using this package body:
create or replace
PACKAGE BODY TEST IS
v_string VARCHAR2(200);
procedure Enr_Rev_AverageEnroll(
Enroll out TEST.return_cursor,
p_month_1 IN Varchar2,
p_month_2 IN Varchar2,
p_month_3 IN Varchar2) IS
BEGIN
v_string := 'select office_name, '||p_month_1||', '||p_month_2||','|| p_month_3||' from enroll_percent_09 order by office_name';

open Enroll for v_string;

END Enr_Rev_AverageEnroll;

END TEST;

I was able to connect and retrieve the dataset using the VB.Net function below

 <WebMethod()> _
Public Function GetEnr_Rev_AverageEnroll(ByVal Date1 As String, ByVal Date2 As String, ByVal Date3 As String) As DataSet
        Dim Month1 As String = MonthName(Month(Date1))
        Dim Month2 As String = MonthName(Month(Date2))
        Dim Month3 As String = MonthName(Month(Date3))
 
        'Dim rslt As String = "notdone"
        'Dim rsltdata As New DataSet()
        ''rsltdata.TableName = "Enr_Rev_AverageEnroll"
        'Dim DALObj As New ODAL()
        Dim strCon As String = "Data Source=localhost;Persist Security Info=True;User ID=modonor2;Password=modonor2"
        Dim ds As New DataSet()
        Dim conn As New OracleConnection(strCon)
        Dim command As New OracleCommand()
        command.Connection = conn
        command.CommandText = "TEST.Enr_Rev_AverageEnroll"
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add(New OracleParameter("Enroll", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
        command.Parameters.Add(New OracleParameter("Month1", OracleDbType.Varchar2)).Value = Month1
        command.Parameters("Month1").Direction = ParameterDirection.Input
        command.Parameters.Add(New OracleParameter("Month2", OracleDbType.Varchar2)).Value = Month2
        command.Parameters("Month2").Direction = ParameterDirection.Input
        command.Parameters.Add(New OracleParameter("Month3", OracleDbType.Varchar2)).Value = Month3
        command.Parameters("Month3").Direction = ParameterDirection.Input
        Dim da As New OracleDataAdapter(command)
        Try
            da.SelectCommand = command
            da.Fill(ds)
        Catch ex As Exception
 
        End Try
        Return ds

Open in new window