wrong return values parameters

afva · Post by **afva** » Wed 10 Nov 2010 10:35

Hello,

When I call an Oracle stored procured with in/output paramters (strings) I get the wrong return-values.
I created a simple test:
Oracle stored procedure:

FUNCTION test(param1_iopar IN OUT VARCHAR2, param2_opar OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
param1_iopar := 'Tekst 1e parameter';
param2_opar := 'Tekst 2e parameter';
return 'return functie tekst';
END;

I call this procedure from VS2008:
String param1_iopar = "Init";
string param2_opar;

try
{
string proc = DS.TEST(ref param1_iopar, out param2_opar);
MessageBox.Show("proc: " + proc + "\nparam1_iopar: " + param1_iopar + "\nparam2_opar: " + param2_opar);

In the message box I see the values:
proc: return functie tekst
param1_iopar: return functie tekst
param2_opar: Tekst 1e parameter

This is not what I expect and want to see.

in my output-window I can see the next statement send to Oracle:
BEGIN
:res:=UPLUS.P_INSK.TEST(:p0, :p1);
END;
-- res: ReturnValue VarChar (Size = 0; DbType = String) []
-- p0: InputOutput VarChar (Size = 4; DbType = String) [Init]
-- p1: Output VarChar (Size = 0; DbType = String) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: am Build: 1.20.40.0

What is the problem???

Thank you [/code]

AndreyR · Post by **AndreyR** » Fri 12 Nov 2010 17:21

Thank you for the report, I have reproduced the problem. I will let you know as soon as it is fixed.

afva · Post by **afva** » Thu 25 Nov 2010 10:51

Hello Andrey,

Can you please look at this problem, because I keep getting errors when I call my Oracle packages.

Thank you,

afva · Post by **afva** » Thu 25 Nov 2010 12:02

hello Andrey,

The problem is in the template.
When I have the FUNCTION TEST() as described above (with a return-value) the template generates the code:

Code: Select all

[Function(Name=@"UPLUS.P_INSK.TEST", IsComposable=true)]
 public System.String TEST([Parameter(Name="PARAM1_IOPAR", DbType="VARCHAR2")] ref string PARAM1_IOPAR, [Parameter(Name="PARAM2_OPAR", DbType="VARCHAR2")] out string PARAM2_OPAR)
{
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PARAM1_IOPAR, null);
   PARAM1_IOPAR = ((string)(result.GetParameterValue(0)));
   PARAM2_OPAR = ((string)(result.GetParameterValue(1)));
   return ((System.String)(result.ReturnValue));
}

When I change de function in Oracle and make a PROCEDURE TEST() (no return-value) the template generates the code:

Code: Select all

[Function(Name=@"UPLUS.P_INSK.TEST")]
public System.Int32 TEST([Parameter(Name="PARAM1_IOPAR", DbType="VARCHAR2")] ref string PARAM1_IOPAR, [Parameter(Name="PARAM2_OPAR", DbType="VARCHAR2")] out string PARAM2_OPAR)
{
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PARAM1_IOPAR, null);
    PARAM1_IOPAR = ((string)(result.GetParameterValue(0)));
    PARAM2_OPAR = ((string)(result.GetParameterValue(1)));
    return ((System.Int32)(result.ReturnValue));
}

This works fine!

As I see it, the return-value from the function is Parameter(0)
((string)(result.GetParameterValue(0)));
The other parameters should start from 1:
PARAM1_IOPAR = ((string)(result.GetParameterValue(1)));
PARAM2_OPAR = ((string)(result.GetParameterValue(2)));

Can you change the template please?

Thank you

AndreyR · Post by **AndreyR** » Thu 25 Nov 2010 12:10

As a temporary workaround you can simply change the generated code manually.
You just need to increment the parameter indexes (it is asociated with the fact that Oracle stored function returns the return value as a first element in the output parameters list):

Code: Select all

            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), Param1Iopar, null);
            Param1Iopar = ((string)(result.GetParameterValue(0)));
            Param2Opar = ((string)(result.GetParameterValue(1)));
            return ((System.String)(result.ReturnValue));

should be replaced by

Code: Select all

            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), Param1Iopar, null);
            Param1Iopar = ((string)(result.GetParameterValue(1)));
            Param2Opar = ((string)(result.GetParameterValue(2)));
            return ((System.String)(result.ReturnValue));

afva · Post by **afva** » Thu 25 Nov 2010 12:38

hello Andrey,

I already did this, but I am testing so I have to change this manually every time.
I would rather change this in the template itself.
How can I test (in template.tmpl) if I do have a return-value (function) or not (procedure)?

Thank you

AndreyR · Post by **AndreyR** » Tue 30 Nov 2010 14:54

Just add the following line after the int count = 0 line in the GenerateContextMethods method:

Code: Select all

if (method.MethodType == MethodType.ScalarResult)
            count = 1;

I will let you know as soon as this problem is fixed.

AndreyR · Post by **AndreyR** » Wed 01 Dec 2010 11:28

The problem is fixed, the fix wiill be included in the nearest build (unfortunately, not the one released yesterday).

afva · Post by **afva** » Tue 20 Dec 2011 12:07

Hello,
We just upgraded our Devart products (Devart dotConnect for Oracle 6.60.258.0; Devart Entity Developer 4.2.85.0 ).

I use (the new) template "Linqconnect" to create my entities.
I noticed that you changed the parameter-count, as you suggested in this post to start with 1;
But now it seems to me that this value should be 0;
Is it possible that Oracle also changed something in the return-parameter, so that it is no longer the first parameter?
So in the template the parameter-count should actually start with 0 in case of a call to an Oracle function? Or is there something else I should do?

Thank You,

StanislavK · Post by **StanislavK** » Wed 21 Dec 2011 16:36

The IExecuteResult interface (which is returned by the DataContext.ExecuteMethodCall method) provides the ReturnValue property and the GetParameterValue method. The first one retrieves the function return value, and the second returns the values of the output parameters. The indexes of these parameters should start from 0, and the initial index used in the code generated by the default LinqConnect template is 0 as well.

The corresponding code from the template is

Code: Select all

IExecuteResult _ = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())) 0 ? ", " + string.Join(", ", parametersNameList.ToArray()) : "" #>);

 = 
  (()
  (_.GetParameterValue( 0 ? parameter.ProcedurePosition - 1 : count #>)));

You may observe some other behaviour, e.g., if you are using some custom template. In this case, please either select the default one or modify the custom one.

afva · Post by **afva** » Fri 23 Dec 2011 11:24

Works ok now.
Thank you