C# Oracle database references to PL/SQL cursor

Question

I have cursor named generuj_cene_wypoz(), the cursor works in database but I don't know how to call it correctly in C# btw the connection between the program and the database works.

    public void question2(string name)
    {
        OracleCommand cmd = con.CreateCommand();
        cmd.CommandText = name;
        cmd.CommandType = CommandType.StoredProcedure;
        OracleDataReader dr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(dr);
        myDataGrid.ItemsSource = dt.DefaultView;
        dr.Close();
    }

    private void Button_Click_1(object sender, RoutedEventArgs e)
    {
        question2("exec generuj_cene_wpoz()");
    }

to connect to the database I am using

Oracle.ManagedDataAccess.Client;
Oracle.ManagedDataAccess.Types;

question from:https://stackoverflow.com/questions/65924882/c-sharp-oracle-database-references-to-pl-sql-cursor

Answer 1

Here is an example that will help.

First a function that return a ref cursor in oracle

create or replace package test_PKG as 

function test_rpt (p_testid number) return sys_refcursor;

end test_PKG;

create or replace package body test_PKG as

function test_rpt (p_testid number) return sys_refcursor as

test_rpt_cur sys_refcursor;

begin 

open test_rpt_cur for

select 1 as id_info from dual;
 
return test_rpt_cur;

end test_rpt;

test_PKG

C# code

public Datatable pop_test_rpt (int p_testid_app)
{

    using (OracleConnection myconn = new OracleConnection(string_connection_info)
    {
        const string _default_cursor_name = "1";
        myconn.Open();
        OracleCommand cmd = new OracleCommand ("test_schema.test_PKG.test_rpt", myconn)
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(_default_cursor_name, OracleDBType.RefCursor, ParamaterDirection.ReturnValue); 
        cmd.Parameters.Add("p_testid", OracleDBType.Int32).Value = p_testidapp;
         DataTable dt = new DataTable();
         dt.Load(cmd.ExecuteReader());
         myconn.close();
         return dt;
      }
  }