How to execute oracle stored procedure returning multiple cursors using c#

Question

I am using OracleClient with C#. I have a stored procedure returning multiple cursors. I have to associate each cursors to different tables. But when It tried to do this I am getting this error.

ORA-24338: statement handle not executed

Can anybody help me to rectify this issue.

Answer 1

I have currently implemented such code. So, let me give you the steps I followed.

1. In the DAC layer, I arranged the input parameters and out put parameters (RefCursors) of the stored procedure.

    using (DbCommand objCmd = dbObject.GetSqlStringCommand(queryString))
       {
       ...
       objCmd.Parameters.Add("Ref_CursorName1",OracleDbType.RefCursor,ParameterDirection.Output);
       objCmd.Parameters.Add("Ref_CursorName2",OracleDbType.RefCursor,ParameterDirection.Output);
        ...
       connectoinObj.Open();
       objCmd.ExecuteNonQuery();
   

2. Iterate through the parameter count.

   for (int i = 0; i < objcmd.Parameters.Count; i++)
       { 
        if(objcmd.Parameters[i].DbType == DbType.Object)
          {
            if(!(OracleRefCursor) objCmd.Parameters[i].Value).IsNull)
               {
                 var cursorParam = ((OracleRefCursor) objCmd.Parameters[i].Value).GetDataReader();
              if(cursorParam.HasRows && cursorParam.FieldCount > 0)
                 {
                   MapCursors(cursorParam, objcmd.Parameters[i].ParameterName, response); //type of your response object.                          
                 }
               }
           }
         }
   

3. The last step is MapCursors method implementation.

   private void MapCursors(OracleDataReader cursorParam, string parameterName, ClassName response)
      {
         case "Ref_CursorName1":
              RetriveDataFromRef_CursorName1(cursorParam, response);
              break; 
      }
   

4. Do the mapping of cursor data to that of your object (response).