?
Solved

Return Ref Cursor to dataset from oracle stored procedure

Posted on 2005-03-26
3
Medium Priority
?
2,619 Views
Last Modified: 2009-07-31
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
0
Comment
Question by:danny__t
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 4

Accepted Solution

by:
imperial_p79 earned 500 total points
ID: 13636336
Add

dbAdaptr.SelectCommand = cmd

before .Fill(ds)

basically

.SelectCommand = cmd
.Fill(ds)

Hope this helps!
0
 
LVL 1

Author Comment

by:danny__t
ID: 13636437
So simple!

Fantastic thanks
0
 
LVL 1

Expert Comment

by:mattfox77
ID: 24991396
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

0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month9 days, 1 hour left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question