Problem with TOraStoredProc going from 5.80.0.42 to 9.7.2

[kithran]

We recently started testing our code with version 9.7.2 of ODAC (we currently use 5.80.0.42) and have uncovered a problem with TOraStoredProc components on forms.

With no change to the Delphi or Oracle code we are now getting an Oracle error on attempting to call a function within a package.

Using SQL Monitor I have identified a difference between the actual call to Oracle between the two versions of ODAC as follows:

5.80.0.42:

58 08:26:01 SQL Execute: begin
:RESULT := P_FM_JOBS.RUN_ENDDAY(:CUSTID, :CUSTGRP, :OPTBAL, :OPTVEH, :OPTEXP, :OPTVRB, :RUNDATE, :INTERVAL);
end;
59 08:26:01 :RESULT(FLOAT,OUT)=<NULL>
:CUSTID(VARCHAR[0],IN)=<NULL>
:CUSTGRP(VARCHAR[0],IN)=<NULL>
:OPTBAL(VARCHAR[1],IN)='Y'
:OPTVEH(VARCHAR[1],IN)='Y'
:OPTEXP(VARCHAR[1],IN)='Y'
:OPTVRB(VARCHAR[1],IN)='Y'
:RUNDATE(DATE,IN)=<NULL>
:INTERVAL(VARCHAR[0],IN)=<NULL>

9.7.2:

81 08:24:51 SQL Execute: begin
P_FM_JOBS.RUN_ENDDAY(:RESULT, :CUSTID, :CUSTGRP, :OPTBAL, :OPTVEH, :OPTEXP, :OPTVRB, :RUNDATE, :INTERVAL);
end;
82 08:24:51 :RESULT(FLOAT,OUT)=<NULL>
:CUSTID(VARCHAR[0],IN)=<NULL>
:CUSTGRP(VARCHAR[0],IN)=<NULL>
:OPTBAL(VARCHAR[1],IN)='Y'
:OPTVEH(VARCHAR[1],IN)='Y'
:OPTEXP(VARCHAR[1],IN)='Y'
:OPTVRB(VARCHAR[1],IN)='Y'
:RUNDATE(DATE,IN)=<NULL>
:INTERVAL(VARCHAR[0],IN)=<NULL>
83 08:24:51 Error: ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'RUN_ENDDAY'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

The issue is clearly to do with the fact there is a Result parameter and how it is handled by ODAC.

Is there something different we need to be doing to call a function in a package with TOraStoredProc now?

Is there a change we can make to our code that will work with both versions of ODAC?

Is this related to this issues mentioned here - http://forums.devart.com/viewtopic.php?f=5&t=27169?

Cheers

[AlexP]

To solve the problem, you should regenerate for stored procedures and functions, since the RESULT parameter must now to have the IsResult = True attribute to generate a correct query

[kithran]

The problem with doing that is we have 1000s of instances of this within our code. Also doing this means we would have to branch our code wherever we do this to be able to continue to support the live version whilst testing whether there are any other issues with 9.7.2

[paulzip]

My company has EXACTLY the same problem as this, we have tens of thousands of calls to TOraStoredProc which have been broken by this change, we are trying to move to later versions of ODAC but this change is a nightmare.

It is unreasonable to expect your customers to have to go through and manually open and / or correct something YOU'VE broken by a change. The amount of man hours and regression testing required are going to be huge - which amounts to great expense to us! Especially when software houses have multiple variants of their products (using different versions of ODAC). This causes branching of files everywhere to implement the change you suggest.

Can you explain why you made such a change, when for years such a requirement wasn't needed? Can you explain why you can't default to IsTrue internally if the parameter is called 'RESULT' is an out parameter and IsTrue is not defined? Perhaps you could add a TOraSession parameter to do this automatically? As a Delphi programmer myself, this seems a relatively easy thing to do.

You've created a huge headache for your ODAC users making this change, especially people who use ODAC to write Enterprise systems containing thousands of forms / datamodules and tens or even hundreds of thousands of TOraStoredProc calls. I think YOU need to address this, not us.

[paulzip]

Can we have a reply from someone at Devart to the recent comments and issues raised in relation to this post?

[paulzip]

Devart support, can you stop selectively ignoring posts and give some feedback to issues which are affecting your customers in a considerable way?

Are you going to address the TOraParam.IsResult issue you've introduced? Your previous suggestions aren't practical to implement, and several of us have explained why. I'm STILL waiting for you to reply to this.

[AlexP]

We have fixed this problem. In the new versions you don't need to modify old dfm files, or regenerate procedure calls. The latest ODAC version will work correctly with old projects.

[kithran]

This does appear fixed in most cases however there is still an issue if the function result is a boolean. Please see this post for an example: http://forums.devart.com/viewtopic.php?f=5&t=29528

[AlexP]

Please don't create similar topics, I have answered you in http://forums.devart.com/viewtopic.php?f=5&t=29528