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