Reputation: 41
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