Devart Forums

Posted: **Tue 22 Jan 2013 08:41**

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!

Posted: **Tue 22 Jan 2013 11:59**

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.

Posted: **Tue 22 Jan 2013 15:03**

Thanks for reply.

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

Posted: **Tue 22 Jan 2013 15:27**

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

Posted: **Wed 23 Jan 2013 06:13**

It works!

Thank you AlexP.

Posted: **Wed 23 Jan 2013 07:31**

Hello,

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

Devart Forums

Problem with TOraStoredProc. Parameters omission and default values

Problem with TOraStoredProc. Parameters omission and default values

Re: Problem with TOraStoredProc. Parameters omission and default values

Re: Problem with TOraStoredProc. Parameters omission and default values

Re: Problem with TOraStoredProc. Parameters omission and default values

Re: Problem with TOraStoredProc. Parameters omission and default values

Re: Problem with TOraStoredProc. Parameters omission and default values