[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 986
  • Last Modified:

Locate records in ORACLE cursor expression using C#/ASP.NET

Hi guys...
I have a ORACLE stored procedure that returns a recordset and cursor expression:

OPEN P_RSET FOR
      SELECT CUST_ID, CURSOR( SELECT p.PRODUCT_CODE
                                                    FROM PRODUCT_TABLE p
                                                  WHERE p.CUST_ID = c.CUST_ID)  products                                  
      FROM CUSTOMER _TABLE c;


Can someone tell me how can get individual record within the products cursor using C#?  Should I use ORACLEDatareader/adapter?  How do I declare the parameters in the code?  Can I have a brief example pls.  Thanks a lot
0
waiman
Asked:
waiman
  • 2
1 Solution
 
Ivo StoykovCommented:
try something like
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
class OracleRefCursorSample
{
  static void Main()
  {
    // how to use REF CURSORs returned from 
    // PL/SQL Stored Procedures or Functions
    
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
   // Create an OracleCommand
    OracleCommand cmd = new OracleCommand("MyFunc", con);
    cmd.CommandType = CommandType.StoredProcedure;
   // Bind the parameters
   // p1 is the RETURN REF CURSOR
    OracleParameter p1 = 
      cmd.Parameters.Add("refcur_ret", OracleDbType.RefCursor);
    p1.Direction = ParameterDirection.ReturnValue;
      
    // p2 is the OUT REF CURSOR
    OracleParameter p2 = 
      cmd.Parameters.Add("refcur_out", OracleDbType.RefCursor);
    p2.Direction = ParameterDirection.Output;      
    // Execute the command
    cmd.ExecuteNonQuery();
 
    // Construct an OracleDataReader from the REF CURSOR
    OracleDataReader reader1 = ((OracleRefCursor)p1.Value).GetDataReader();
 
    // Prints "reader1.GetName(0) = EMPNO"
    Console.WriteLine("reader1.GetName(0) = " + reader1.GetName(0));
 
    // Construct an OracleDataReader from the REF CURSOR
    OracleDataReader reader2 = ((OracleRefCursor)p2.Value).GetDataReader();
    
    // Prints "reader2.GetName(0) = DEPTNO"
    Console.WriteLine("reader2.GetName(0) = " + reader2.GetName(0));
 
    reader1.Close();
    reader1.Dispose();
 
    reader2.Close();
    reader2.Dispose();
 
    p1.Dispose();
    p2.Dispose();
 
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

Open in new window

0
 
waimanAuthor Commented:
Thanks... so in your example, I assume P2 is the returned recordset and P1 is the returned cursor expression for EACH P2 record right?  So do I need to have a loop to go through each P1?
0
 
Ivo StoykovCommented:
yes you should use datareader to read ;-) the data

HTH

Ivo Stoykov
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now