使用OracleDataReader(ODAC)返回Oracle存储过程

本文关键字：返回 Oracle 存储过程 ODAC OracleDataReader 使用 | 更新日期: 2023-09-27 18:20:09

我使用OracleDataReader（Oracle.DataAccess.Client）从Oracle存储过程输出数据时遇到问题。

程序：

    procedure LOAD_BL_REQ_2(P_XML CLOB, P_XML_OUT out CLOB) is
    BEGIN
     P_XML_OUT    := 'TEST1111';
     exception
     when others then
        P_XML_OUT   := 'LOAD_BL_REQ: Error'|| SQLERRM;
    END;

C#代码：

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = OraConnection;
    cmd.CommandText = "IBS.BNT_EQ.LOAD_BL_REQ_2";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    OracleParameter result = new OracleParameter();
    result.ParameterName = "P_XML_OUT";
    result.OracleDbType = OracleDbType.Clob;
    result.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(result);
    OracleParameter XMLString = new OracleParameter();
    XMLString.ParameterName = "P_XML";
    XMLString.OracleDbType = OracleDbType.Varchar2;
    XMLString.Direction = System.Data.ParameterDirection.Input;
    OracleDataReader dr;
    cmd.Transaction = OraConnection.BeginTransaction();
    try
    {
        XMLString.Value = XML;
        cmd.Parameters.Add(XMLString);
        dr = cmd.ExecuteReader();
        cmd.Transaction.Commit();
    }
    catch (OracleException ex)
    {
        cmd.Transaction.Rollback();
        Log(2, "Transaction fail, exception: " + ex.ToString());
        ORADisconnect();
        return "";
    }

在调试过程中，我看到了dr:

    Depth: 0
    FetchSize: 131072
    FieldCount: 0
    HasRows: false

对于测试，我添加计数代码：

    while (dr.Read())
    {
        count++;
    }

计数在任何时间都是0。

此代码（例如）：

  if(dr.IsDBNull(0))
  {
   //etc...
  }

或

  OracleClob oclob;
  oclob = dr.GetOracleClob(0);

返回异常：

  dr.isdbnull exception: System.InvalidOperationException: Operation is not valid due to the current state of the object.
  at Oracle.DataAccess.Client.OracleDataReader.IsDBNull(Int32 i)
  at GlobalFunc.ORA_BlackList_Test(String XML) in c:'inetpub'project'App_Code'GlobalFunc.cs:line 474

最后，我需要从Oracle SP检索CLOB数据，但现在已停止执行此步骤。使用：Visual Studio 2012，ASP.Net（web项目，但我将此代码写入WinForms应用程序并遇到相同问题），oracle.dataaccess.dll 4.112.3.0

使用OracleDataReader(ODAC)返回Oracle存储过程

我找到了解决方案！然而，一切都很简单：

        string XML = "XML Data";
        OracleCommand cmd = OraConnection.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "IBS.BNT_EQ.LOAD_BL_REQ_2";
        OracleParameter result = new OracleParameter();
        result.ParameterName = "P_XML";
        result.OracleDbType = OracleDbType.Clob;
        result.Value = XML;
        result.Direction = System.Data.ParameterDirection.InputOutput;
        cmd.Parameters.Add(result);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.ToString());
        }
        string str = (result.Value as OracleClob).Value;
        MessageBox.Show("Val: " + str);
        OraConnection.Close();

也许它对某人有用。

您必须调用dataReader.Read()才能从DataReader实际获取一些数据。

还要注意，ODP.NET默认情况下会忽略参数名称并按位置绑定。这意味着在你的excample中参数是错误的。创建命令后调用cmd.BindByName = true，或按正确顺序将它们添加到命令的Parameters集合中。

过程视图，，谢谢，RUN！

 protected void Save(object sender, EventArgs e)
    {
        OracleConnection conn = new OracleConnection("Data Source=XE;Persist Security Info=True;Password=*****;User ID=WINCELL");
        string procedure = "WINCELL_API.ADD_CAMPAIGN";
        OracleCommand cmd = new OracleCommand(procedure, conn);
       // DataTable dt = new DataTable();
       // OracleDataAdapter da = new OracleDataAdapter();
        string xml = "<campaign>";
        xml += "'n";
        xml += "<campaignInfo>";
        xml += "'n";
        xml += "<name>" + TextBox1.Text + "</name>";
        xml += "'n";
        xml += "<startDate>" + TextBox2.Text + "</startDate>";
        xml += "'n";
        xml += "<period>" + TextBox3.Text + "</period>";
        xml += "'n";
        xml += "<handsetStatu>" + TextBox4.Text + "</handsetStatu>";
        xml += "'n";
        xml += "<ServiceID>" + TextBox5.Text + "</ServiceID>";
        xml += "'n";
        xml += "</campaignInfo>";
        xml += "'n";
        xml += "</campaign>";

        TextBox6.Text = xml;
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            OracleParameter param = new OracleParameter();
            param.ParameterName = "";
            param.OracleDbType = OracleDbType.Clob;
            param.Value = xml;
            param.Direction = System.Data.ParameterDirection.InputOutput;
            cmd.Parameters.Add(param);
            try
            {
            cmd.ExecuteNonQuery(); 
            }
            catch (Exception ex)
            {
            Label1.Text = error" + ex.ToString();
            }
        //string str = (param.Value as OracleClob).Value;
        //Label1.Text = "val: " + str;
    }
相关文章：