ahmed qaisi
ahmed qaisi

Reputation: 41

How can I get data from a stored procedure that contains a Cursor with another Cursor inside it

How can I get data from a stored procedure that contains a cursor with another cursor inside it in ASP.NET ?

When I try this code, I get the following error:

Unsupported Oracle data type RSET encountered

I need to fetch data from oracle and transfer it to a web service Oracle 9i

This is the stored procedure and the code that I work on:

PROCEDURE P_GET_VST_CLAIMS_CLAIMSDTL ( P_PAGE_SIZE NUMBER, P_PAGE_NUMBER NUMBER, P_VST_ID NUMBER, P_PAT_ID NUMBER, P_REFCUR OUT SYS_REFCURSOR )
IS
/*
P_REFCUR_DATA STRUCTURE
----------------------------------------------------------------
        VST_ID,
        OL2CLAIMS_ID,
        CLM_DATE,
        MPTYPE_ID,
        MP_TYPE_DESC_E,
        MP_TYPE_DESC_A,
        CLM_MP_ID,
        PROVIDER_NAME_E,
        PROVIDER_NAME_A,
        MP_BRANCH_ID,
        MP_PART_ID,
        STATUS,
        STATUS_DESCE,
        STATUS_DESCA,
        PAT_ID,
        PAT_NAME_E,
        PAT_NAME_A,
        CLM_AMT,
        CO_INS_TOT_AMT,
        ATTACHS
        CLAIMDTL (REFCUR)
            ID                            NUMBER
            VST_ID                        NUMBER
            CLM_ID                        NUMBER
            DTL_TYPE                    VARCHAR2
            DTL_ID                        NUMBER
            DESC_E                        VARCHAR2
            DESC_A                        VARCHAR2
            HCPC_ID                        NUMBER
            MDSN_ID                        NUMBER
            QTY                            NUMBER
            UNIT_DESC_E                    VARCHAR2
            UNIT_DESC_A                    VARCHAR2
            TQ_ID                        NUMBER
            TQ_DESC_E                    VARCHAR2
            TQ_DESC_A                    VARCHAR2
            REQUESTED_REPETITION        NUMBER
            CLM_AMT                        NUMBER
            PRICE_LIST_AMT                NUMBER
            PRICE_LIST_REJ_AMT            NUMBER
            CO_INS_TOT_AMT                NUMBER
            CO_INS_DESERVED_AMT            NUMBER
            TPA_MP_SERVICE_AMT            NUMBER
            MP_DISC_AMT                    NUMBER
            MP_NET_AMT                    NUMBER
*/

C# code:

[HttpPost]
[Obsolete]
public IHttpActionResult P_GET_VST_CLAIMS_CLAIMSDTL([FromBody] OracleParameters model)
{
    IHttpActionResult httpActionResult;

    try
    {
        string UsernameConfig = WebConfigurationManager.AppSettings["AppUsername"];
        string PasswordConfig = WebConfigurationManager.AppSettings["AppPassword"];

        if (model.AppUserName != UsernameConfig)
        {
            httpActionResult = this.Return_Response(false, 5, "Application Username Or Password not correct", null);
        }
        else if (model.AppPassword == PasswordConfig)
        {
            using (OracleConnection connection = new OracleConnection("Data Source= (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = db1))); User Id=coreapps; Password=coreapps;"))
            {
                OracleDataAdapter da = new OracleDataAdapter();

                OracleCommand cmd = new OracleCommand()
                    {
                        Connection = connection,
                        CommandText = "PKG_COREAPPS.P_GET_VST_CLAIMS_CLAIMSDTL",
                        CommandType = CommandType.StoredProcedure
                    };

                cmd.Parameters.Add("P_PAGE_SIZE", OracleType.Number).Value = model.P_PAGE_SIZE;
                cmd.Parameters.Add("P_PAGE_NUMBER", OracleType.Number).Value = model.P_PAGE_NUMBER;
                cmd.Parameters.Add("P_VST_ID", OracleType.Number).Value = model.P_VST_ID;
                cmd.Parameters.Add("P_PAT_ID", OracleType.Number).Value = model.P_PAT_ID;
                cmd.Parameters.Add("P_REFCUR", OracleType.Cursor).Direction = ParameterDirection.Output;
                
                connection.Open();

                OracleDataAdapter da1 = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da1.Fill(ds);
    
                ClsLog.TextLog(ds.Tables[0].Rows[0][0].ToString());
                connection.Close();

                httpActionResult = this.Return_Response1(true, 1, "Success", ds);
            }
        }
        else
        {
            httpActionResult = this.Return_Response(false, 5, "Application Username Or Password not correct", null);
        }
    }
    catch (Exception exception)
    {
        Exception ex = exception;
        ClsLog.OnError(ex);
        httpActionResult = this.Return_Response(false, 3, ex.Message, null);
    }

    return httpActionResult;
}

Upvotes: 1

Views: 563

Answers (0)

Related Questions