Calling Procedure with ref cursor param via LinqConnect

[mohan.p]

Hi Devart Team,

Recently I have upgraded to dotConnect for Oracle version 8.4.313.0.
The problem that I am facing with upgraded version is, I am not able to execute a Stored Procedure which was working fine with the previous version(dotconnect for oracle 6.30.165.0).

I am getting the below error....while calling this procedure......

{"ORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of arguments in call to 'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2, column 3:\nPLS-00306: wrong

number or types of arguments in call to 'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of arguments in call to

'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of arguments in call to 'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2,

column 3:\nPLS-00306: wrong number or types of arguments in call to 'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of

arguments in call to 'GPY_PR_GETTRANSACTIONDETALS'\nORA-06550: line 2, column 3:\nPL/SQL: Statement ignored"}

Stack Trace:

Code: Select all

at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.DataProvider.a(c1 A_0, Object[] A_1)
   at Devart.Data.Linq.DataProvider.c(Expression A_0)
   at Devart.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at PayoneContext.PayoneDataContext.GPY_PR_GETTRANSACTIONDETALS(Nullable`1 IDFILTER_IN, String FROMDATE_IN, String TODATE_IN, String DATERANGE_IN, Nullable`1 

AUT_AMOUNT_FROM_IN, Nullable`1 MOV_AMOUNT_FROM_IN, Nullable`1 AUT_AMOUNT_TO_IN, Nullable`1 MOV_AMOUNT_TO_IN, String TRANSACTIONTYPE_IN, String UICCODE_IN, Nullable`1 

BANKTRXID_IN, Nullable`1 APPROVEDTRANSACTION_IN, String SHOPTRXID_IN, String ERRORCODE_IN, String AUTHORIZATIONCODE_IN, Nullable`1 PAYMENTMETHOD_IN, Nullable`1 

ALERT_IN, String TRANSACTIONSTATE_IN, String THREEDLEVEL_IN, String RED_FRAUDRESPONSE_IN, Nullable`1 IDSHOP_IN, Nullable`1 IDLANGUAGE_IN, String CUSTOMFIELDSID_IN, 

String CUSTOMFIELDSVALUE_IN, String GETRESULTSFOR_IN, Nullable`1 ROWNUMFROM_IN, Nullable`1 ROWNUMTO_IN, String SORTBY_IN, String SORTDIR_IN, String ROWFILTERDATE_IN, 

String& OUT_ERRORCODE, String& OUT_ERRORMESSAGE) in D:\TEMP\DevartV8\PayoneDataContext.Designer.cs:line 93

This is my procedure without body......

Code: Select all

procedure GPY_PR_GETTRANSACTIONDETALS(IDFILTER_IN            IN NUMBER,
                                          FROMDATE_IN            IN VARCHAR2 DEFAULT null,
                                        TODATE_IN              IN VARCHAR DEFAULT null,
                                        DATERANGE_IN           IN VARCHAR DEFAULT null,
                                        AUT_AMOUNT_FROM_IN     IN NUMBER,
                                        MOV_AMOUNT_FROM_IN     IN NUMBER,
                                        AUT_AMOUNT_TO_IN       IN NUMBER,
                                        MOV_AMOUNT_TO_IN       IN NUMBER,
                                        TRANSACTIONTYPE_IN     IN VARCHAR2,
                                        UICCODE_IN             IN VARCHAR2,
                                        BANKTRXID_IN           IN NUMBER,
                                        APPROVEDTRANSACTION_IN IN NUMBER,
                                        SHOPTRXID_IN           IN VARCHAR2,
                                        ERRORCODE_IN           IN VARCHAR2,
                                        AUTHORIZATIONCODE_IN   IN VARCHAR2,
                                        PAYMENTMETHOD_IN       IN NUMBER,
                                        ALERT_IN               IN NUMBER,
                                        TRANSACTIONSTATE_IN    VARCHAR2,
                                        THREEDLEVEL_IN         VARCHAR2,
                                         RED_FRAUDRESPONSE_IN   VARCHAR2,
                                        IDSHOP_IN              NUMBER,
                                        IDLANGUAGE_IN          NUMBER,
                                        CUSTOMFIELDSID_IN      VARCHAR2,
                                        CUSTOMFIELDSVALUE_IN   VARCHAR2,
                                        GETRESULTSFOR_IN       VARCHAR2,
                                        ROWNUMFROM_IN          NUMBER,
                                        ROWNUMTO_IN            NUMBER,
                                         SORTBY_IN              VARCHAR2 DEFAULT 'trx.DataAut',
                                        SORTDIR_IN             VARCHAR2 DEFAULT  'DESC',
                                        ROWFILTERDATE_IN       varchar2 default '',
                                        RESULTSET_OUT          OUT SYS_REFCURSOR,
                                        out_errorcode          OUT VARCHAR2,
                                        out_errormessage       OUT VARCHAR2,
                                        ADDITIONALFIELDS       OUT SYS_REFCURSOR,
                                        SUMMARY_OUT            OUT SYS_REFCURSOR);

Tools used in the application:

.NET 4.0
Asp.Net MVC3
dotConnect for Oracle Trial Edition (Version: 8.4.313.0)
Windows Server 2012
IIS 8.5
Oracle 11g


Could you please explain the cause for this issue?

[MariiaI]

Thank you for the report. This is a known issue and it is related to the code generation for stored procedures with OUT cursor parameters (or with mixed cursor and scalar parameters) and we are working on it, but we cannot provide any timeframe at the moment. We will inform you when any results are available.

To avoid the error, we recommend you one of the following solutions:
1) (the most suitable solution) Recreate your stored procedure so that OUT SYS_REFCURSOR parameters were the last ones, i.e.:

Code: Select all

...
SORTDIR_IN             VARCHAR2 DEFAULT  'DESC',
ROWFILTERDATE_IN       varchar2 default '',
out_errorcode          OUT VARCHAR2,
out_errormessage       OUT VARCHAR2,
RESULTSET_OUT          OUT SYS_REFCURSOR,
ADDITIONALFIELDS       OUT SYS_REFCURSOR,
SUMMARY_OUT            OUT SYS_REFCURSOR);

After this it is necessary to re-add the necessary stored procedure to the model and regenerate the code for the corresponding method.

2) Manually change the corresponding method, so that ordinal numbers of the result sets in the code correspond to ordinal number of the parameters in the stored procedure. We are sending you a sample project with the DDL/DML scripts to the e-mail address you have provided in your forum profile; please check that the letter is not blocked by your mail filter.

If this doesn't help, please send us the test project (or the model with the generated code), so that we are able to reproduce this issue in our environment and find the solution for you in a shortest time.

[mohan.p]

Hi Mariial,
Thanks for your PROMPT response, I would like to add one more thing related to this issue.

I have changed ref cursor param number in auto generated code (30, 33, 34) as per the Procedure,

Code: Select all

 	[Function(Name=@"GPY_PR_GETTRANSACTIONDETALS")]
        [Devart.Data.Linq.Mapping.ResultType(typeof(ACTIVE_REPORTS_CONTEXT),30)]
        [Devart.Data.Linq.Mapping.ResultType(typeof(CUSTOMFILTERS_CONTEXT), 33]
        [Devart.Data.Linq.Mapping.ResultType(typeof(SUMMARY_DETAILS_CONTEXT), 34]
        public Devart.Data.Linq.IMultipleResults GetActiveReportsDetails([Parameter(Name="IDFILTER_IN", DbType="NUMBER")] System.Nullable<decimal> IDFILTER_IN,....)
        {
            IExecuteResult _GetActiveReportsDetailsResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), IDFILTER_IN, FROMDATE_IN, TODATE_IN, DATERANGE_IN, AUT_AMOUNT_FROM_IN, MOV_AMOUNT_FROM_IN, AUT_AMOUNT_TO_IN, MOV_AMOUNT_TO_IN, TRANSACTIONTYPE_IN, UICCODE_IN, BANKTRXID_IN, APPROVEDTRANSACTION_IN, SHOPTRXID_IN, ERRORCODE_IN, AUTHORIZATIONCODE_IN, PAYMENTMETHOD_IN, ALERT_IN, TRANSACTIONSTATE_IN, THREEDLEVEL_IN, RED_FRAUDRESPONSE_IN, IDSHOP_IN, IDLANGUAGE_IN, CUSTOMFIELDSID_IN, CUSTOMFIELDSVALUE_IN, GETRESULTSFOR_IN, ROWNUMFROM_IN, ROWNUMTO_IN, SORTBY_IN, SORTDIR_IN, ROWFILTERDATE_IN, (string)null, (string)null);
            OUT_ERRORCODE = ((string)(_GetActiveReportsDetailsResult.GetParameterValue(31)));
            OUT_ERRORMESSAGE = ((string)(_GetActiveReportsDetailsResult.GetParameterValue(32)));
            return ((Devart.Data.Linq.IMultipleResults)(_GetActiveReportsDetailsResult.ReturnValue));
        }

so that I am able to execute the procedure sucessfully, at the same time when I call this procedure from one another method I am getting the following error.......

This is the method which calls......

Code: Select all

PayoneDataContext db = new PayoneDataContext(DbConnection.GetOracleConnection(dbAlias));

 result = db.GetActiveReportsDetails(idFilter, null, null, null, null, null, null,
                       null, null, null, null, null, null, null, null, null, null, null,
                       null, null, idShop, idLanguage, string.Empty, string.Empty, "EXPORT", 0, 0, null, null, null, out sErrorCode, out sMessage);

List<ACTIVE_REPORTS_CONTEXT> result = result.GetResult<ACTIVE_REPORTS_CONTEXT>().ToList();
 List<CUSTOMFILTERS_CONTEXT> resultCustomFilters = result.GetResult<CUSTOMFILTERS_CONTEXT>().ToList();
 List<SUMMARY_DETAILS_CONTEXT> resultSummary = result.GetResult<SUMMARY_DETAILS_CONTEXT>().ToList();

Exception: FormatException

Error Message: Input string was not in a correct format.

Stack Trace:

Code: Select all

 at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt64(String value, NumberStyles options, NumberFormatInfo numfmt)
   at System.Int64.Parse(String s, IFormatProvider provider)
   at Devart.Data.Oracle.y.aa(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Common.q.aq(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Common.q.b(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Data.Oracle.y.b(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Data.Oracle.dm.b(Byte[] A_0, Int32 A_1, Int32 A_2)
   at Devart.Data.Oracle.OracleDataReader.GetInt32(Int32 i)
   at MaterializeACTIVE_REPORTS_CONTEXT(MaterializerScope )
   at Devart.Data.Linq.Engine.ObjectReader`1.a()
   at Devart.Data.Linq.Engine.ObjectReader`1.a(T& A_0)
   at Devart.Data.Linq.Engine.ObjectReader`1.c()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ReportsModel.GetExportData(Int64 idFilter, Int64 idLanguage, Decimal idShop) in D:\TEMP\ReportsModel.cs

This is for your information.
I have only modified auto generated code not procedure.

[MariiaI]

Error Message: Input string was not in a correct format.

We couldn't reproduce this issue in our environment.
Please send us a test project, so that we are able to reproduce it in our environment and find the solution for you in a shortest time.
JIC: It is not necessary to send us the whole project, you could exclude those parts of the project that should not affect the main scenario.

[gayathri]

Hi Team,
I am also facing the same issue what mohan.P faced before .(input string was not in a correct format ) when calling the method with different parameters.The same scenario but in below environment


I am using vs 2015,
.net framework 4.5.2 ,
devart version 9.2

what could be the cause of this issue ?Can you please provide any solutions for this ?

[Shalex]

@gayathri:
1. Could you please try to localize the issue and send us a small complete test project with the corresponding DDL/DML script for reproducing the issue?
2. Specify the full stack trace of the exception.