Unespected results using TSQLStoredProc

[cmagno]

I'm using a TSQLStoredProc component to call a Stored Procedure in an Oracle database. The Stored Procedure is something like this:

Code: Select all

PROCEDURE pcalcula_intpagoextra(pNroCredito IN NUMBER, pFecha IN VARCHAR2, pValorInt OUT NUMBER,
                        pFechaini IN OUT DATE, pDiasInt OUT NUMBER, pFechaProxFact OUT DATE,
                        pObservacion OUT VARCHAR2, pOpcion IN VARCHAR2 DEFAULT 'P')
  IS
    lr_condicionescred      condicionescred%ROWTYPE;
    lr_duenocredito         duenocredito%ROWTYPE;
    ld_fecpagoextra         DATE;
    ln_ultimacuotafact      NUMBER(3);
    ln_saldopagoextra       NUMBER;
    ln_cantPagExtra         NUMBER:=0;
    FechaCambioTasa         DATE;
    TasaAnterior            NUMBER;
    ln_dias1                NUMBER;
    ln_int1                 NUMBER;
    ln_int2                 NUMBER;
    ln_dias2                NUMBER;
    pFechad                 DATE;
    ld_FechaIni             DATE;
    ln_saldocapital         NUMBER;
BEGIN

...


END;

In my delphi program I have a grid with some records. Every time the user selects a row, the OnCellClick event is activated and that event calls the "calculofecha" function (see below) that executes the "pcalcula_intpagoextra" Stored Procedure (object osp_calcularinteres). The first time the "calculofecha" function is called, it works perfectly, but if the user selects another record on the grid the Stored Procedured returns an unespected result (I mean not the expected result for the sent parameters). If the user selects again the first record the unespected result also appears. So, the Stored Procedure works fine the first time only. How can I fix that?. Do I have to clear something of the osp_calcularinteres object?. I appreciate your help. I'm using dbExpress driver for Oracle 4.40.

Code: Select all

function calculofecha: boolean;
begin
      osp_calcularinteres.Params[0].AsBCD :=
                      qry_pagoextraordinario.fieldbyname('CRED_CONSECUTIVO').AsInteger;
      osp_calcularinteres.Params[1].AsString :=
                      datetostr(vg_FechaPago); 
      osp_calcularinteres.Params[7].AsString := 'P';
      osp_calcularinteres.ExecProc;

      if (osp_calcularinteres.Params[6].AsString = 'OK') then begin
         qry_pagoextraordinario.FieldByName('PAEX_FECPERIODOINI').AsDateTime :=
                        osp_calcularinteres.Params[3].AsDatetime;
         cte_dias.Text        := osp_calcularinteres.Params[4].AsString;
         ded_proximopago.Date := osp_calcularinteres.Params[5].AsDateTime;
         qry_pagoextraordinario.FieldByName('PAEX_INTERESES').AsFloat:=
                      osp_calcularinteres.Params[2].AsFloat;
       end
       else begin
         btn_aplicapagos.Enabled:=false;
         ShowMessage(osp_calcularinteres.Params[6].AsString);
         result:=false;
         exit;
       end;
     end;
  result:=true;
end;

[Plash]

I see that the pFechaini parameter of the stored procedure is input/output. But you don't assign any value to it. Maybe this causes the problem. Try to assign NULL to this parameter before executing the stored procedure.

If this doesn't help, please send to dbxoda*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.