使用C#从Oracle中获取记录作为游标输出 [英] Fetching records from Oracle as a cursor Output using C#

问题描述

如何使用C#.Net和ODBC从具有输出参数作为游标的Oracle存储过程中获取记录?

我有一个存储过程sp_myproc，其中有一个游标作为输出参数.

在我的应用程序中，我使用ODBC连接到Oracle.
问题是，游标没有这种类型的odbctype.

所以，我如何获取记录?

How to fetch records using C#.Net and ODBC from a Oracle Stored Procedure which has Output parameters as cursor?

I have a stored procedure sp_myproc which has a cursor as a ouput parameters.

In my application , I''m using ODBC for connection to Oracle.
The Problem is ,there is no such type of odbctype for cursor .

so, How can I get records?

OdbcConnection odbcCon = new OdbcConnection(connectionString);
OdbcCommand odbcCmd = new OdbcCommand();
odbcCmd.CommandText = "SP_myOracle";
odbcCmd.CommandType = CommandType.StoredProcedure;
//here I want to Add parameters as Cursor and Directon as Output

//
 DataTable dt = new DataTable();
 OdbcDataAdapter adap = new OdbcDataAdapter(odbcCmd);
 adap.Fill(dt);

我的存储过程是:

my Stored Procedure is:

create or replace
PROCEDURE SP_myOracle
(EmpDETAILSCURSOR OUT  SYS_REFCURSOR)
AS
BEGIN
 OPEN EmpDETAILSCURSOR for 
Select EMP_NAME from Emp;
END

谢谢.

Thanks.

推荐答案

static void Main(string[] args)
{
  string constr = "User Id=hr; Password=hr;
    Data Source=oramag; Pooling=false";

  OracleConnection con = new OracleConnection(constr);
  con.Open();

  OracleCommand cmd = con.CreateCommand();

  cmd.CommandText = "begin open :1 for
    select * from employees
    where manager_id=101; end;";

  OracleParameter p_rc = cmd.Parameters.Add(
    "p_rc",
    OracleDbType.RefCursor,
    DBNull.Value,
    ParameterDirection.Output);

  cmd.ExecuteNonQuery();

  cmd.Parameters.Clear();

  cmd.CommandText = "cursor_in_out.process_cursor";
  
  cmd.CommandType = CommandType.StoredProcedure;

  OracleParameter p_input = cmd.Parameters.Add(
    "p_input",
    OracleDbType.RefCursor,
    p_rc.Value,
    ParameterDirection.Input);

  cmd.ExecuteNonQuery();

  p_input.Dispose();
  p_rc.Dispose();
  cmd.Dispose();
  con.Dispose();
}

您在这里

使用Oracle REF CURSOR [

Here you go

Working with Oracle REF CURSORs[^]

这篇关于使用C#从Oracle中获取记录作为游标输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！