I am actually trying to fetch result from the SP. When I call the SP from the database, it is working fine and returns the result. However, when I call the SP from my WCF API, it is returning me the type of OracleDbType.RefCursor. I really don't know what exactly is the problem. I tried investigating but was not able to find the result. Below is the information:
WCF API
public string GetHQNameValue(string branch_source_ref, string branch_source_name)
{
try
{
using (var cmd = _uow.DataContext.Context().Coection.CreateCommand())
{
cmd.CommandText = Constants.StoredProcedure.SP_GET_HQ_NAME_BRANCH.Name;
((OracleCommand)cmd).BindByName = true;
cmd.CommandType = CommandType.StoredProcedure;
AddGetHQNameValue(branch_source_ref, branch_source_name, cmd);
cmd.ExecuteReader();
return Convert.ToString(converter.GetHQNameVal(cmd.Parameters));
}
}
finally
{
_uow.DataContext.Context().Coection.Dispose();
}
}
private void AddGetHQNameValue(string branch_source_ref, string branch_source_name, DbCommand cmd)
{
AddParameter(cmd, Constants.StoredProcedure.SP_GET_HQ_NAME_BRANCH.Columns.PVI_BRANCH_SOURCE_REF,
OracleDbType.Varchar2,
ParameterDirection.Input,
branch_source_ref);
AddParameter(cmd, Constants.StoredProcedure.SP_GET_HQ_NAME_BRANCH.Columns.PVI_BRANCH_SOURCE_NAME,
OracleDbType.Varchar2,
ParameterDirection.Input,
branch_source_name);
AddParameter(cmd, Constants.StoredProcedure.SP_GET_HQ_NAME_BRANCH.Columns.PRO_RESULT,
OracleDbType.RefCursor,
ParameterDirection.Output,
DBNull.Value);
}
STORED PROCEDURE
PROCEDURE SP_GET_HQ_NAME_BRANCH
(
PVI_BRANCH_SOURCE_REF IN E_BUSINESS_ALIAS_EXT.SOURCE_REF%TYPE,
PVI_BRANCH_SOURCE_NAME IN E_BUSINESS_ALIAS_EXT.SOURCE_NAME%TYPE,
PRO_RESULT OUT SYS_REFCURSOR
)
IS
V_SQL VARCHAR(32767);
V_ENTITY_NAME VARCHAR(32767):='BRANCH';
BEGIN
V_SQL:=' SELECT * FROM E_BUSINESS_ALIAS_EXT T1
INNER JOIN L_BUSINESS_TO_BUSINESS T2 ON T1.BUSINESS_ID=T2.RELATED_BUSINESS_ID
INNER JOIN M_BUSINESS T3 ON T3.BUSINESS_RID=T2.BUSINESS_ID
WHERE T1.SOURCE_REF= '''||PVI_BRANCH_SOURCE_REF||''' AND T1.SOURCE_NAME= '''||PVI_BRANCH_SOURCE_NAME||''' AND T1.ENTITY_NAME='''||V_ENTITY_NAME||''' ';
--OPEN PRO_RESULT FOR V_SQL;
DBMS_OUTPUT.PUT_LINE(V_SQL);
END;
The SP is returning the result, but when called from API, its returning the type of ref-cursor and not the result. I really don't know where exactly is the problem. I have tried other SP and all of them are working fine except this. Please help!!
