strange behaviour

strange behaviour

Postby MaGl » Wed 03 Apr 2013 10:20

Hi all

I'm new using Devart .Connect for Oracle and I'm running into a strange bevaviour using
OracleCommand with a procedure that has (in my case) 2 input parameters and one output
parameter which is a ref_cursor.
For 1 of the 2 input parameters I use a default value a la

" ... ,mAKT_PID in number default null, ..."

If I run the command and put a value via parameter into this mAKT_PID I get a wrong resultset. The reason? .Connect use ALWAYS the default value and not the actual value
I set to the parameter.

I think this is a serious bug!
MaGl
 
Posts: 11
Joined: Wed 03 Apr 2013 10:13

Re: strange behaviour

Postby Pinturiccio » Wed 03 Apr 2013 14:26

We couldn't reproduce the issue. Could you please create and send us a small test project with the corresponding DDL/DML scripts for reproducing the issue?

Please also tell us what connection mode you use, Direct or OCI.
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: strange behaviour

Postby MaGl » Thu 04 Apr 2013 06:12

Hi

it is quite easy to reproduce the issue

on Oracle-DB do this

create or replace procedure devart_test (mvar1 in number,mvar2 in number default null,result out number) as
begin
result := mvar1 + nvl(mvar2,0);
end;

on .net do this

Dim command = New OracleCommand()
With command
.Connection = ORAdb
.CommandType = CommandType.StoredProcedure
.CommandText = "devart_test"
.PassParametersByName = True
.Parameters.Clear()
.ParameterCheck = True

.Parameters.Add("mvar1", OracleDbType.Number, 10, ParameterDirection.Input)
.Parameters.Add("mvar2", OracleDbType.Number, 2, ParameterDirection.Input)
.Parameters.Add("result", OracleDbType.Number, ParameterDirection.Output)


.ExecuteNonQuery()

MsgBox(.Parameters("result").Value)

.Dispose()

End With


Run the application and you will get... 10!!!

If you change the oracle procedure into

create or replace procedure devart_test (mvar1 in number,mvar2 in number,result out number)

you get 12!!!

the difference is the "default null"

I use the OCI - method, have no experience with direct mode

thx in advance
MaGl
 
Posts: 11
Joined: Wed 03 Apr 2013 10:13

Re: strange behaviour

Postby MaGl » Thu 04 Apr 2013 06:24

Hi

the same is for direct mode... it seems that oraclecommand uses ALWAYS the default value
if there is one defined in the oracle procedure/function
and it has nothing to do with some NULL-Issue... you can set "default 10" und you will always get mvar1 + 10 and not mvar1 + the actual provided number

this should be fixed asap!
MaGl
 
Posts: 11
Joined: Wed 03 Apr 2013 10:13

Re: strange behaviour

Postby Pinturiccio » Thu 04 Apr 2013 11:03

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

As a temporary workaround, remove the following line from your code:
Code: Select all
.ParameterCheck = True
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: strange behaviour

Postby MaGl » Thu 04 Apr 2013 13:07

Thank you for investigating.
Parametercheck = false
or
omitting Parametercheck at all
does the trick

Matthias
MaGl
 
Posts: 11
Joined: Wed 03 Apr 2013 10:13

Re: strange behaviour

Postby Pinturiccio » Wed 17 Apr 2013 13:10

We have fixed the bug with stored procedure having a parameter with the default value, and the ParameterCheck and PassParametersByName OracleCommand properties simultaneously equal to 'true'. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: strange behaviour

Postby Pinturiccio » Thu 18 Apr 2013 14:51

The new build of dotConnect for Oracle 7.7.224 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=26910
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle