title: Return of Oracle Stored Procedure using OracleDataReader (ODAC)
body: <p>I have a problem with output data from Oracle Stored Procedure using OracleDataReader(Oracle.DataAccess.Client). </p>
  
  <p>Procedure:</p>
  
  <pre><code>    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;
  </code></pre>
  
  <p>C# Code:</p>
  
  <pre><code>    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 "";
      }
  </code></pre>
  
  <p>At debug process i see for dr:</p>
  
  <pre><code>    Depth: 0
      FetchSize: 131072
      FieldCount: 0
      HasRows: false
  </code></pre>
  
  <p>For test i add count code:</p>
  
  <pre><code>    while (dr.Read())
      {
          count++;
      }
  </code></pre>
  
  <p>Count is 0 in any times.</p>
  
  <p>This code (for example):</p>
  
  <pre><code>  if(dr.IsDBNull(0))
    {
     //etc...
    }
  </code></pre>
  
  <p>or</p>
  
  <pre><code>  OracleClob oclob;
    oclob = dr.GetOracleClob(0);
  </code></pre>
  
  <p>return exception:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>At final i need to retrieve CLOB data from Oracle SP, but now stopped at this step.
  Use: Visual Studio 2012, ASP.Net (web project, but i write this code to WinForms application and have a same problem), oracle.dataaccess.dll 4.112.3.0</p>