none
what is the C# code to read TYPES.cursortypes in procedure RRS feed

  • Question

  • I have a stored procedure like this

           

    PROCEDURE PRC_ABCD_GETALL (resultset_out OUT TYPES.cursorType) AS BEGIN OPEN resultset_out FOR SELECT * FROM ABCD; END PRC_ABCD_GETALL;

    And my C# code is like this

    using (OracleConnection conn = new OracleConnection(cnn)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "PRC_ABCD_GETALL"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("resultset_out", OracleDbType.RefCursor, ParameterDirection.Output); OracleDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { result.Add(Construct(rdr)); } }

    but I'm getting error 'OracleDbType' does not contain a definition for 'Cursor' in
    OracleType.Cursor. Any solution for this??

    • Changed type KareninstructorMVP Tuesday, January 2, 2018 6:43 PM This is a question
    • Moved by Fei Hu Thursday, January 4, 2018 7:27 AM Oracle related
    Tuesday, January 2, 2018 8:27 AM

All replies

  • Hi Sudipta,

    Please read the MSDN documentation on Oracle REF CURSORs

    There it states that:

    To execute a stored procedure that returns REF CURSORs, you must define the parameters in the OracleParameterCollection with an OracleType of Cursor and a Direction of Output. The data provider supports binding REF CURSORs as output parameters only. The provider does not support REF CURSORs as input parameters.

    Please look into this article about how you can assign the Direction of a output.

    Returning Result Sets from SQL Server and Oracle

    Call Oracle Stored Proc from C# that returns RefCursor

    Hope this helps you.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Tuesday, January 2, 2018 1:06 PM
  • thanks for reply sabah :) . let me go through these documents that you provided. TYPES. Once again thanks for your time again :)
    Tuesday, January 2, 2018 3:10 PM
  • The .NET Oracle provider is deprecated. Please use ODP.NET instead. Questions related to third party products like ODP.NET should be posted in their forums.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, January 2, 2018 3:55 PM
  • As you have learned (and also by searching for OracleDbType the docs have no cursor member),

    The following is in vb.net yet would point you to the Parameters.Add on the second parameter for Direction.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ref-cursor-parameters-in-an-oracledatareader

    Last thought, do you have references for Oracle.DataAccess and Oracle.ManagedDataAccess ???

    EDIT

    See the following

    http://www.oracle.com/technetwork/articles/dotnet/williams-refcursors-092375.html


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, January 2, 2018 6:19 PM
  • hi karen, thanks for reply :) .I am using Oracle.DataAccess. I used system.data.client as data provider and I faced some issues like DBFactory.Createdatabase() this namespce is depricated for ages. So i decided to do it with Oracle.DataAccess . My actual code was like this

    Database db = DatabaseFactory.CreateDatabase();

    DbCommand objComm = db.GetStoredProcCommand("package_name.sp", new object[1]);         

    var result = new List<PortalList>();         

    using (IDataReader rdr = db.ExecuteReader(objComm)){           

    while (rdr.Read()){                   

    result.Add(Construct(rdr));               

    }           

    }         

    return result;


    and my store procedure was like this

    PROCEDURE PRC_ABCD_GETALL (resultset_out OUT TYPES.cursorType) AS BEGIN OPEN resultset_out FOR SELECT * FROM ABCD; END PRC_ABCD_GETALL;

    I am still confused about TYPES.cursortype what does it do?I saw RefCursor everywhere but never saw TYPES.cursortype. Can you tell me what is the difference between them?which one is better to use? I am fresher and I searched everywhere for the solution but no luck :(  

    Wednesday, January 3, 2018 7:19 AM
  • hi michael, thanks for reply :) . I will post this question to their forum.
    Wednesday, January 3, 2018 7:26 AM
  • Hi Saha,

    Your question is more related to third party products, you could post a new thread to Oracle forum for suitable support.

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 4, 2018 7:26 AM