calling stored procedures with default parameters

nowy · Post by **nowy** » Thu 10 Aug 2006 13:38

Hello,

Currently we are migrating one of our applications from DOA to ODAC
components. We have a big problem with calling stored procedures with
default parameters. In ODAC default parameters are always NULL, but DOA makes it correctly. Let me give an example:

Code: Select all

PROCEDURE get_free_ress(
  in_quantflag   IN NUMBER,
  IN_TEST        IN NUMBER DEFAULT 5
)

if I call it in ODAC in this way:

Code: Select all

var
   Package_Orion_Gantt: TOraPackage;

Package_Orion_Gantt.ExecProcEx('get_free_ress', ['in_quantflag', 1]);

IN_TEST is always NULL, but it should be 5.

This breaks functionality of many our stored procedures, the same
call in DOA gives proper values of default parameters.

I use ODAC 5.70.0.29, Delphi 7, Windows XP SP2,
Oracle Client 9.2.0.7.0 (NET Option is not used)
Oracle 9i Server 9.2.0.7.0.

Any help will be approciated.
Thanks

Challenger · Post by **Challenger** » Thu 10 Aug 2006 15:07

We found out the problem. Now we are investigating it. As soon as we solve it we will let you know.

Plash · Post by **Plash** » Mon 14 Aug 2006 11:57

We will add support for parameters' default values in next version of ODAC.

Plash · Post by **Plash** » Mon 14 Aug 2006 13:56

If last parameters in the stored procedure have default values, then you can use TOraStoredProc component to call the procedure with default parameters. Create parameters for TOraStoredProc, then call ExecProc method. For example:

Code: Select all

  OraStoredProc.StoredProcName := 'MyPackage.MyProc';
  OraStoredProc.Params.CreateParam(ftInteger, 'A', ptInput).Value := 1;
  OraStoredProc.ExecProc;

nowy · Post by **nowy** » Mon 21 Aug 2006 08:38

Hi,
Thank your for your answer.
I'm glad to hear that default parameters will be supported.
The solution with explicitly setting default parameters means changing a lot of code, so we would rather wait for the next version of ODAC.

Regards
Krzysztof