2 Replies Latest reply: May 6, 2010 2:47 PM by 770968 RSS

    Basic Stored Procedure to Populate a Dataset - Returning an Error

    721872
      Hello All:

      I come from a SQL Server background and have created extensive stored procedures in SQL Server to work with .Net, so I fully admit my limitations with Oracle and working with .Net in asking this question, as this is my first attempt in trying to get an Oracle Stored Procedure to work, sending back data to my .Net code, so anything you can do to help will be greatly appreciated.

      I have the stored procedure below, which compiled (I am not sure it is even correct though to do what I want, which is basically return a recordset that I want in the end to populate a dataset using the .Fill method of my adapter).

      CREATE OR REPLACE PROCEDURE spAllocationSelectAll(
      p_Cursor OUT SYS_REFCURSOR)
      IS

      BEGIN

      OPEN p_Cursor FOR
      SELECT
      AllocationID,
      AllocationName
      FROM
      lkAllocation
      ORDER BY
      AllocationName;
      END spAllocationSelectAll;

      I don't know how to call this stored procudure from my .Net Code. I am getting an errory saying saying ORA-06550 as the error code.

      .Net Code:
      oConn = new OracleConnection(ConfigurationManager.ConnectionStrings.ConnectionString);
      OracleCommand oCommand = new OracleCommand();
      oCommand.Connection = oConn;
      oCommand.CommandText = "spAllocationSelectAll";
      oCommand.CommandType = CommandType.StoredProcedure;
      oCommand.Parameters.Add("curGroupSizings", OracleType.Cursor).Direction = ParameterDirection.Output;
      oAdapter = new OracleDataAdapter(oCommand);
      ds = new DataSet();
      oAdapter.Fill(ds, "GroupsSizings");

      I don't know what I am missing. Can anyone point out what is missing either in my Stored Procedure or my .Net code that is causing this to occur?
        • 1. Re: Basic Stored Procedure to Populate a Dataset - Returning an Error
          gdarling - oracle
          Hi,

          I didnt look closely at your code, but had this laying around, see if it helps.

          Also make sure to take a look at the examples on your hard drive at <ORACLEHOME>\ODP.NET\samples\2.x\RefCursor

          Just FYI, the [ODP.NET forum|http://forums.oracle.com/forums/forum.jspa?forumID=146] would be a more appropriate place for this question.

          Hope it helps,
          Greg
          /*
          CREATE OR REPLACE procedure simplerefcur( v_deptno in number,ecur out sys_refcursor) is
              BEGIN
               OPEN ecur for select * from emp where deptno=v_deptno;
             end;
          /
          */
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          using Oracle.DataAccess.Types;
          
          public class testrefcur
          {
          public static void Main()
          {
              using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;"))
              {
                  con.Open();
                  using (OracleCommand cmd = new OracleCommand("simplerefcur",con))
                  {
                      cmd.CommandType = CommandType.StoredProcedure;
                      OracleParameter p1 = new OracleParameter("p1", OracleDbType.Decimal);
                      p1.Value = 10;
                      cmd.Parameters.Add(p1);
                      OracleParameter p2 = new OracleParameter("p2", OracleDbType.RefCursor, ParameterDirection.Output);
                      cmd.Parameters.Add(p2);
                      cmd.Parameters[0].Value = 10;
                      OracleDataAdapter da = new OracleDataAdapter(cmd);
                      DataSet ds = new DataSet();
                      da.Fill(ds);
                      Console.WriteLine("ds has {0} rows",ds.Tables[0].Rows.Count.ToString());
                  }
              }
          }
          }
          • 2. Re: Basic Stored Procedure to Populate a Dataset - Returning an Error
            770968
            Try this

            Dim sResult As New DataSet()

            Oraclecon.Open()


            Dim myCMD As New OracleCommand()
            myCMD.Connection = Oraclecon
            myCMD.CommandText = "RTM_SP1_TEST"
            myCMD.CommandType = CommandType.StoredProcedure

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

            Dim MyDA As New OracleDataAdapter(myCMD)


            MyDA.Fill(sResult)

            DataGridView1.DataSource = sResult.Tables(0)