Problems when calling function of a modeled stored procedure

[Anchor]

Hello,

I'm not sure where this questions belongs so I am putting it in this group too.

I am hoping someone can help me. I am trying to execute a function but I am getting an error. This function was created using the Function Import operation which is mapped to a stored procedure that was added to the EDMX from the database. I think the problem is associated to the ref cursor being returned. Just to let you know, I am using EF4 thru VS2010. I am also using the 6.30.185 version of dotConnect for Oracle Professional.

Anyway here is the signature of the stored procedure...

Code: Select all

 procedure p_get_user_chk_prod (
                       i_user_name             in t_ccc_user.user_name%type,
                       i_user_password         in t_ccc_user.user_password%type,
                       i_enforce_authorization in number,
                       i_log_activity          in number,
                       i_prod_code             in t_ccc_product.product_code%type,
                       o_result_set            out ref cursor,
                       o_imp_code              out t_ccc_implementation.imp_code%type,
                       o_msg_id                out t_ccc_message.msg_id%type,
                       o_msg_desc              out t_ccc_message.msg_desc%type) is

This is how I am calling the function in my code....

Code: Select all

private UserDO Validate(string prodCode, string sUserName, string sPassword, out 
                        string impCode, out int nErrCode, out string sErrMsg)
        {
    ObjectParameter o_Err_Code = new ObjectParameter("o_msg_id", -99);
    ObjectParameter o_Err_Msg = new ObjectParameter("o_msg_desc", "None");
    ObjectParameter o_Imp_Code = new ObjectParameter("o_imp_code", "");
    CCCDataEntities context = null;
    UserDO thisUser = null;
    impCode = String.Empty;
    try
    {
        using (CCCDataEntities cxt = new CCCDataEntities())
        {
            //byte[] bResultSet = new byte[10000];

            ObjectParameter o_Result_Set =
                    new ObjectParameter("o_result_set", typeof(byte[]));
            var usrTmp = cxt.sp_Get_User_Chk_Prod(sUserName, sPassword, 1, 1,
                                                  prodCode, o_Result_Set, 
                                                  o_Imp_Code, o_Err_Code, 
                                                  o_Err_Msg);

This used to work when I was using VS2008 and earlier versions of .NET amd dotConnect for Oracle. One thing was different though. Do you see the commented out byte array three lines above the function call? I passed that into the sp_Get_User_Chk_Prod function instead of the ObjectParameter for the Result Set, which is the ref cursor in the stored procedure. However, when I moved this code over to VS2010/EF4 from VS2008/EF1, the compiler comlpained that it expected an ObjectParameter instead of a byte array so I changed it. So, I'm thinking this is the problem but I could be wrong.

Here is the function import information in the EDMX...

Code: Select all

 <FunctionImport Name="sp_Get_User_Chk_Prod" EntitySet="UserDOSet" ReturnType="Collection(CCCDataModel.UserDO)">
            <Parameter Name="I_USER_NAME" Mode="In" Type="String" />
            <Parameter Name="I_USER_PASSWORD" Mode="In" Type="String" />
            <Parameter Name="I_ENFORCE_AUTHORIZATION" Mode="In" Type="Decimal" />
            <Parameter Name="I_LOG_ACTIVITY" Mode="In" Type="Decimal" />
            <Parameter Name="I_PROD_CODE" Mode="In" Type="String" />
            <Parameter Name="O_RESULT_SET" Mode="Out" Type="Binary" />
            <Parameter Name="O_IMP_CODE" Mode="Out" Type="String" />
            <Parameter Name="O_MSG_ID" Mode="Out" Type="Decimal" />
            <Parameter Name="O_MSG_DESC" Mode="Out" Type="String" />
          </FunctionImport>

So when I run my program and exception is thrown. Here is the error I am seeing...

Code: Select all

ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'P_GET_USER_CHK_PROD'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Here is the stack trace.

Code: Select all

   at Devart.Data.Oracle.av.b(Int32 A_0)
   at Devart.Data.Oracle.ax.d(Int32 A_0)
   at Devart.Data.Oracle.ax.a(Int32 A_0, bg A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.e.a(CommandBehavior A_0)
   at Devart.Common.Entity.ak.b(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.e.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Do you see anything that is wrong? Do you think it is a problem with how I am passing in the O_RESULT_SET into the function as an ObjectParameter? Any help you could give would be greatly appreaciated.

Thanks,
Joe

[Anchor]

BTW, I tried what was suggested here... http://www.devart.com/dotconnect/oracle ... rsors.html

And I still got the same error...

Code: Select all

ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'P_GET_USER_CHK_PROD'
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'P_GET_USER_CHK_PROD'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

[Anchor]

I don't know if I found a fix or a work-around but I got my function to work. What I did was edit the EDMX through an XML Editor and looked for the Function definition for the stored procedure. It looks like this...

Code: Select all

       <Function Name="P_GET_USER_CHK_PROD" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="P_GET_USER_CHK_PROD" Schema="COMMON_DEV_OWNER">
          <Parameter Name="I_USER_NAME" Type="VARCHAR2" Mode="In" />
          <Parameter Name="I_USER_PASSWORD" Type="VARCHAR2" Mode="In" />
          <Parameter Name="I_ENFORCE_AUTHORIZATION" Type="decimal" Mode="In" />
          <Parameter Name="I_LOG_ACTIVITY" Type="decimal" Mode="In" />
          <Parameter Name="I_PROD_CODE" Type="VARCHAR2" Mode="In" />
          <Parameter Name="O_RESULT_SET" Type="REF CURSOR" Mode="Out" />
          <Parameter Name="O_IMP_CODE" Type="VARCHAR2" Mode="Out" />
          <Parameter Name="O_MSG_ID" Type="decimal" Mode="Out" />
          <Parameter Name="O_MSG_DESC" Type="VARCHAR2" Mode="Out" />
        </Function>

I changed the Mode attribute from the Parameter element for the O_RESULT_SET from "Out" to "In". I then went into the Model Browser for the EDMX and deleted my previously defined Function Import and created a new Function Import for the modeled stored procedure P_GET_USER_CHK_PROD. I selected the return type as Entity like before and hit "OK".

You will then get a compiler error at your function call (defined from the previous function import) because it now expects a byte[] as a parameter for the O_RESULT_SET instead of an ObjectParameter. Now my call looks like this....

Code: Select all

   byte[] bResultSet = new byte[10000];
   decimal dEnforceAuth = 1;
   decimal dLogActivity = 1;
   var usrTmp = context.sp_Get_User_Chk_Prod(sUserName, sPassword, dEnforceAuth, dLogActivity, sProdCode, bResultSet, O_IMP_CODE, O_MSG_ID, O_MSG_DESC);

That seemed to have solved the problem. I hope this helps someone.