Help calling stored functions
Help calling stored functions
Hello,
Prior to version 6.3.6 (in my case it was V4.50.19) we were used to use TSQLDataset to call all of our functions (functions are packaged).
With the new version when we try this, we always get the "PLS-00306 wrong number or types of arguments" error.
Reading through the forums we discovered the from some version on, we're not be able to use these componentes anymore and that the code should be changed to use TSQLStoredProc instead.
The problem is that this component is many times slower the the preceding one. A call that usually takes less than 50ms to execute, can reach 300-600ms.
I read somewhere that this is most probably due to the metadata queries and that it's adviced to use TSQLQuery component for the same purpose. The problem is that with this component i'm not able to call stored functions wrapped in a block like 'begin :RESULT:=SOMEFUNCTION; end;' or i'm not able to correctly set it out.
I searched through the forum but i wasn't able to find anything.
Which approach should i take for this issue? is there anything describing the use, limits and advantages of each 'TSQL' component when using devart drivers?
Consider that our applications exposes a lot of APIs through functions in packages and the most of the work is done by calling those functions.
dbexpress for oracle v6.3.6 and XE4 update1 on 32but environment
thanks in advance
Prior to version 6.3.6 (in my case it was V4.50.19) we were used to use TSQLDataset to call all of our functions (functions are packaged).
With the new version when we try this, we always get the "PLS-00306 wrong number or types of arguments" error.
Reading through the forums we discovered the from some version on, we're not be able to use these componentes anymore and that the code should be changed to use TSQLStoredProc instead.
The problem is that this component is many times slower the the preceding one. A call that usually takes less than 50ms to execute, can reach 300-600ms.
I read somewhere that this is most probably due to the metadata queries and that it's adviced to use TSQLQuery component for the same purpose. The problem is that with this component i'm not able to call stored functions wrapped in a block like 'begin :RESULT:=SOMEFUNCTION; end;' or i'm not able to correctly set it out.
I searched through the forum but i wasn't able to find anything.
Which approach should i take for this issue? is there anything describing the use, limits and advantages of each 'TSQL' component when using devart drivers?
Consider that our applications exposes a lot of APIs through functions in packages and the most of the work is done by calling those functions.
dbexpress for oracle v6.3.6 and XE4 update1 on 32but environment
thanks in advance
Last edited by fulvion on Mon 16 Jun 2014 13:35, edited 1 time in total.
Re: Help calling stored functions
Hello,
We cannot reproduce the problem. Please send the full script for creating the procedure, as well as the Delphi code to alexp*devart*com .
We cannot reproduce the problem. Please send the full script for creating the procedure, as well as the Delphi code to alexp*devart*com .
Re: Help calling stored functions
Hi Alex,
do you mean the source code for "cursor not returned from query" error, the one with TSQLStoredProc or the one with TSQLQuery and a function call in an anonymous plsql block?
do you mean the source code for "cursor not returned from query" error, the one with TSQLStoredProc or the one with TSQLQuery and a function call in an anonymous plsql block?
Re: Help calling stored functions
Please create a small sample demonstrating the problem and send it to alexp*devart*com .
Re: Help calling stored functions
Sent via email
thanks
Fulvio
thanks
Fulvio
Re: Help calling stored functions
This behavior is due to the fact that the standard TCustomSQLDataSet parses incorrectly the string with package name and procedures pkgTest.sfTest, and, eventually, attempts to find only the procedure excluding package. Unfortunatelly, we can't affect this behavior.
Re: Help calling stored functions
Alexp, sorry for being so unclear.
with devart version 4.50.19 this problem was not present. But we had to move to this new version to use your library with XE4.
So you're stating that this problem has been introduced by the new version of dbexpress?
with devart version 4.50.19 this problem was not present. But we had to move to this new version to use your library with XE4.
So you're stating that this problem has been introduced by the new version of dbexpress?
Re: Help calling stored functions
We have checked this behavior on Delphi 7 and XE: in Delphi 7, when setting name of a procedure with a package, parameters are created; in XE - are not. Embarcadero has changed this behavior in one of the previous versions of Delphi.