Devart Forums

Posted: **Wed 15 Jan 2014 18:27**

Hello,

I have problem with calling Oracle stored functions. I have TOraStoredProc component, which is stored in dfm file in the following way:

Code: Select all

  object spPunktTorPowiazane: TOraStoredProc
    StoredProcName = 'u_man.F_PUNKT_OBS_TOR_POWIAZANE'
    Session = DMMain.SRead
    SQL.Strings = (
      'begin'
      
        '  :RESULT := u_man.F_PUNKT_OBS_TOR_POWIAZANE(:I_TR_TR_ID, :I_PL_' +
        'PL_ID, :I_DATA);'
      'end;')
    Left = 55
    Top = 155
    ParamData = <
      item
        DataType = ftInteger
        Name = 'RESULT'
        ParamType = ptOutput
        Value = nil
      end
      item
        DataType = ftFloat
        Name = 'I_TR_TR_ID'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftFloat
        Name = 'I_PL_PL_ID'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftDateTime
        Name = 'I_DATA'
        ParamType = ptInput
        Value = nil
      end>
  end

Function definition in Oracle is:

Code: Select all

create or replace function u_man.F_PUNKT_OBS_TOR_POWIAZANE(
  i_tr_tr_id      in u_man.t_punkty_ladunkowe_tory.plt_tr_tr_id%type,
  i_pl_pl_id      in u_man.t_punkty_ladunkowe_tory.pl_pl_id%type,
  i_data          in date)
  return pls_integer
is
  v_result pls_integer;
begin
    -- Pobranie ilości powiązań punktu ładunkowego i toru (maksymalnie 1)
  select
    count(plt.rowid)
    into
      v_result
    from u_man.t_punkty_ladunkowe_tory plt
    where
      plt.plt_tr_tr_id = i_tr_tr_id and
      plt.pl_pl_id = i_pl_pl_id and
      i_data between plt.plt_data_od and plt.plt_data_do;

  return (v_result);
end F_PUNKT_OBS_TOR_POWIAZANE;

As you can see in SQL code exist Oracle function call which looks:
:RESULT := u_man.F_PUNKT_OBS_TOR_POWIAZANE(:I_TR_TR_ID, :I_PL_PL_ID, :I_DATA);
and this is correct.
But when I'm trying to call procedure using code:

Code: Select all

  with spPunktTorPowiazane do
  begin
    ParamByName('i_tr_tr_id').AsInteger := ATorId;
    ParamByName('i_pl_pl_id').AsInteger := APunktObsId;
    ParamByName('i_data').AsDate := AData;
    ExecProc;
    Result := ParamByName('result').AsInteger > 0;
  end;

Then exception is occured:

ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'F_PUNKT_OBS_TOR_POWIAZANE'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

In DBMonitor is visible wrong sql call:

Code: Select all

begin
  u_man.F_PUNKT_OBS_TOR_POWIAZANE(:RESULT, :I_TR_TR_ID, :I_PL_PL_ID, :I_DATA);
end;

I don't know why correct SQL code is changed to wrong SQL code. If I remove SQL.Strings and ParamData properties values from .dfm file then everything works correctly, but I don't want to remove these properties for all my functions. Can you correct this problem? I'm using Oracle 10g, Delphi XE4 Pro and ODAC 9.2.5.

best regards
Adam Siwon

Posted: **Thu 16 Jan 2014 14:44**

Hello,

Thank you for the information. We have already fixed the problem in the version 9.2.5, but since you have an old dfm file, you still get this error. Add the string

Code: Select all

IsResult = True

to the DFM, to the section with the RESULT parameter

replace

Code: Select all

      item
        DataType = ftInteger
        Name = 'RESULT'
        ParamType = ptOutput
        Value = nil
      end

with

Code: Select all

      item
        DataType = ftInteger
        Name = 'RESULT'
        ParamType = ptOutput
        IsResult = True
      end

Devart Forums

Stored functions call problem

Stored functions call problem

Re: Stored functions call problem