خرید بک لینک

Vote count: 0

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!!

asked 10 secs ago

برچسب: نویسنده: استخدام کار تاريخ: چهارشنبه 30 فروردين 1396 ساعت: 12:43

صفحه بندی