return oracle ref cursor to dataset from stored procedure

[DannyTmoov2]

I have the following stored procedure in oracle: -

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;
/

I am trying to call this from VB.net into a dataset using the following function: -

'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

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

 

[ThatRickGuy]

Ya might want to check the exception.innerexception.message property.

I've noticed that SQL errors often get wrapped up into another exception. We added a generic recursive error message collector to our library for that very reason.

-Rick

----------------------

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[ca8msm]

You don't actually execute the cmd object which is why it is probably failing on the Fill method (it hasn't ran the procedure and therefore can't fill the dataset. Try running either the ExecuteReader or ExecuteNonQuery methods depending on what you will be doing with your data.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[DannyTmoov2]

correct, I hadn't initialized the command:

.SelectCommand = cmd