Returning XML from Oracle Procedure to C# restful service

Question

I'm trying to build a C# restful service that calls an Oracle procedure and returns the XML result. I've tested the procedure in SQL Developer successfully, and can connect to the database. I receive oracle error ORA-01013: user requested cancel of current operation when running the service. The error happens when I call ExecuteReader().

.Net version 2010

Oracle version 11.2

Full Error

Oracle.ManagedDataAccess.Client.OracleException was caught
  Message=ORA-01013: user requested cancel of current operation
  Source=Oracle Data Provider for .NET, Managed Driver
  ErrorCode=1013
  DataSource=""
  Number=1013
  Procedure=""
  StackTrace:
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
   at WcfDailyDataImporterRestService.DailyDataImporterRestService.GetIndividualFund(String FundNumber) in C:\Working\Source Code\WcfDailyDataImporterRestService\WcfDailyDataImporterRestService\DailyDataImporterRestService.cs:line 39
 InnerException: 

Procedure

create or replace
PROCEDURE SP_GETINDIVIDUALFUND(v_fundID IN VARCHAR2,
                             v_ResultSet OUT sp_GetResultSet.ResultSet) AS
BEGIN

  Open v_ResultSet FOR
  SELECT XMLELEMENT("ROOT",
           XMLAGG(XMLELEMENT("ROW",
             XMLELEMENT("FUNDNAME", A.FUNDNAME),
             XMLELEMENT("FUNDNUMBER", substr(A.FundStationFundNumber, 1, 4)),
             XMLELEMENT("SHARECLASS", A.SHARECLASS), 
             XMLELEMENT("ASOFDATE", B.ASOFDATE), 
             XMLELEMENT("NAV", B.NAV), 
             XMLELEMENT("NAVCHANGE", B.NAVCHANGE), 
             XMLELEMENT("INCEPTIONDATE", A.INCEPTIONDATE)
             )
           )
         )
  FROM TBL_FUND A, 
       TBL_FUNDDAILYINFO B
  WHERE A.FUNDID = B.FUNDID (+)
    AND substr(A.FundStationFundNumber, 1, 4) = v_fundID
    AND A.PRODUCTLINECODE = '3'
    AND (A.PRODUCTCATEGORYCODE <> '5'
    AND A.PRODUCTCATEGORYCODE <> '6'
    AND A.PRODUCTCATEGORYCODE <> '102')
  ORDER BY A.FUNDNAME, 
           A.SHARECLASS;
END;

Service

using Oracle.ManagedDataAccess.Client;
using System;
using System.Configuration;
using System.Data;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Web;
using System.Web.Hosting;


namespace WcfDailyDataImporterRestService
{
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class DailyDataImporterRestService : IDailyDataImporterRestService
    {
        #region DailyData

        [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Xml, UriTemplate = "/{FundNumber}")]
        public string GetIndividualFund(string FundNumber)
        {
            //Setup variables
            var RetXml = "";
            OracleConnection conn = null;

            try
            {
                //Access config file and connect to database
                conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
                conn.Open();

                //Setup Stored Procedure call
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "SP_GETINDIVIDUALFUND";
                cmd.Parameters.Add("v_fundID", OracleDbType.Varchar2, FundNumber, ParameterDirection.Input);
                cmd.Parameters.Add("v_ResultSet", OracleDbType.RefCursor, RetXml, ParameterDirection.Output);

                //Execute Procedure
                using (OracleDataReader odr = cmd.ExecuteReader())
                {

                    if (odr.Read())
                    {
                        RetXml = odr.GetString(0);
                    }
                }
            } 
            catch (Exception e)
            {
                if (conn != null) conn.Close();
            }
            //Return XML
            return RetXml;
        }
        #endregion
    }
}

Things I've Tried

• Returning an XmlType instead of a cursor in the procedure
• Just returning cmd.ExecuteNonQuery(); (same error)
• Adding cmd.CommandTimeout = 5000; (same error)
• Recompiling code and procedure.

Thank you for any help or suggestions.

Answer 1

Got it. I had to change my procedure to the following.

create or replace
PROCEDURE SP_GETINDIVIDUALFUND(v_fundID IN VARCHAR2,
                             v_ResultSet OUT sp_GetResultSet.ResultSet) AS
BEGIN
  Open v_ResultSet FOR
  SELECT dbms_xmlgen.getxml('select A.FUNDNAME, 
    substr(A.FundStationFundNumber, 1, 4) AS FundStationFundNumber, 
    A.SHARECLASS, 
    B.ASOFDATE, 
    B.NAV, 
    B.NAVCHANGE, 
    A.INCEPTIONDATE 
  FROM TBL_FUND A, 
       TBL_FUNDDAILYINFO B  
  WHERE A.FUNDID = B.FUNDID (+) 
    AND A.PRODUCTLINECODE = 3 
   AND (A.PRODUCTCATEGORYCODE <> 5
    AND A.PRODUCTCATEGORYCODE <> 6
    AND A.PRODUCTCATEGORYCODE <> 102)
  ORDER BY A.FUNDNAME, 
           A.SHARECLASS') 
  xml FROM dual;
END;