Problem with TOraStoredProc. Parameters omission and default values

[[email protected]]

Hello.

I have a problem with TOraStoredProc.

I creates parameters for TOraStoredProc manually using AddParam function. For example, I have a stored procedure on my server:

Code: Select all

create or replace procedure "PROC1" 
  (
    operation     NUMBER,    
    object_id     NUMBER
  ) AS
...

and I creates parameters:

Code: Select all

var parameter : TOraParam;
    _storedProcedure : TOraStoredProc;
...
parameter := TOraParam.Create ( nil );

parameter.Name := 'operation';
parameter.ParamType := ptInput;
parameter.Value := value1;
_storedProcedure.Params.AddParam ( parameter );

parameter.Name := 'object_id';
parameter.ParamType := ptInput;
parameter.Value := value2;
_storedProcedure.Params.AddParam ( parameter );

_storedProcedure.Execute ();

Execution is successfully.

But if I omit one of these parameters like this:

Code: Select all

parameter.Name := 'operation';
parameter.ParamType := ptInput;
parameter.Value := value1;
_storedProcedure.Params.AddParam ( parameter );

_storedProcedure.Execute ();

it raises an exception: ORA-06550 wrong number or types of arguments!

If I use auto creation:

Code: Select all

_storedProcedure.ParamByName ( 'operation' ).AsInteger := value1;
_storedProcedure.ParamByName ( 'object_id' ).AsInteger := value2;

Execution is successfully too. I can omit any parameters and there is no any exceptions, TOraStoredProc replaces omitted parameters with NULL value automatically.

Is it bug, or I forget something? I need create parameters manually and sometimes omit one of them. How to fix it?

Thanks!

[AlexP]

Hello,

When generating parameters automatically, independently on whether all parameter values or some of them are specified, the parameters are generated anyway and get the NULL default value. If you don't want to use automatic parameters generating, you should explicitly create all the parameters described in a procedure or a function, including the parameter for returning of data from functions.

[[email protected]]

Thanks for reply.

I understand this. But how this automatic mechanism works? Is it possible to get stored procedure signature before calling _storedProcedure.Execute ()?

[AlexP]

Hello,

For this, you can use the Prepare method, for example:

Code: Select all

  OraStoredProc1.StoredProcName := 'SP_TEST';
  ShowMessage(IntToStr(OraStoredProc1.ParamCount));//ParamCount = 0
  OraStoredProc1.Prepare;//all procedure parameters will be generated after this call
  ShowMessage(IntToStr(OraStoredProc1.ParamCount));// ParamCount = N

[[email protected]]

It works!

Thank you AlexP.

[AlexP]

Hello,

Glad to see that you solved the problem. If you have any other questions, feel free to contact us