Stored Proc & Oracle - please help!

andru123
Date: 10/28/03 

Date: Tue, 28 Oct 2003 11:49:00 +0100

Please Help, I can not call Stored Procedurte using ADO!

The problem is that it says that parameters are not correct or type is not
correct, but it does not say which parameters!!!!

I dont know what to do, I tried every combinations (ftString, ftMemo, ....)
If I dont specify OUT parameters, it does not work. If I specify OUT
parameters, it does not work. OMG :.(
 :(((((((((((

------------------- Stored Procedure --------------------
CREATE OR REPLACE FUNCTION kiw_imt_del_proc
       (p_oid NUMBER
       ,p_h_trans_van DATE
       ,p_h_geldig_van DATE
       ,p_h_gebruiker VARCHAR2
       ,vv_result OUT NUMBER
       ,vv_sqlerror OUT NUMBER
       ,vv_isamerror OUT NUMBER
       ,vv_errordata OUT VARCHAR2
       )
       RETURN NUMBER IS
vv_h_geldig_van DATE;
vv_oid INTEGER;
vv_aantal INTEGER;
wordt_gebruikt EXCEPTION;
kan_niet_wissen EXCEPTION;
BEGIN
vv_result := 1;
    DELETE
    FROM kiw_imt
      WHERE oid = p_oid
  ;
vv_isamerror := 0;
vv_sqlerror := 0;
vv_errordata :='';
RETURN vv_result;

EXCEPTION
  WHEN wordt_gebruikt THEN
    vv_sqlerror := -96;
    vv_errordata:= SQLERRM;
    vv_result:= -1;
    RETURN -1;
  WHEN OTHERS THEN
    UPDATE kiw_imt
    SET h_actief='w'
    WHERE oid = p_oid;
    vv_result:= -1;
    vv_sqlerror:= SQLCODE;
    RETURN -1;

END kiw_imt_del_proc;
/

GRANT EXECUTE ON kiw_imt_del_proc TO PUBLIC;
-------------------------------------------------------------

--------------------------------------------------------------

procedure TfrmWocasOut.Button1Click(Sender: TObject);
Var
  Params : Variant;
begin

  Params := VarArrayCreate( [0,4], VarVariant);
  Params[0] := VarArrayOf(['p_oid', ftInteger, pdInput, 0, 5]);
  Params[1] := VarArrayOf(['p_h_trans_van', ftDateTime, pdInput, 0, Now()]);
  Params[2] := VarArrayOf(['p_h_geldig_van', ftDateTime, pdInput, 0,
Now()]);
  Params[3] := VarArrayOf(['p_h_gebruiker', ftString, pdInput, 2, 'me']);
  Params[4] := VarArrayOf(['p_h_actief', ftString, pdInput, 1, 'w']);
  //Params[5] := VarArrayOf(['vv_result', ftInteger, pdOutput, 1, 0]);
  //Params[6] := VarArrayOf(['vv_sqlerror', ftInteger, pdOutput, 1, 0]);
  //Params[7] := VarArrayOf(['vv_isamerror', ftInteger, pdOutput, 1, 0]);
  //Params[8] := VarArrayOf(['vv_errordata', ftVariant, pdOutput, 4000,
'aaa']);
// don't specify the OUT parameters, this is done by Delphi automagically
  ExecuteStoredProc (self.ADODataSet1, 'kiw_imt_del_proc', Params,
    self.ADOCommand1);
  VarClear(Params);
end;

procedure ExecuteStoredProc(Query : TADODataSet; StoredProc: String;
  var Params: Variant; SQLCommand : TADOCommand);
Var i : Integer;
begin
  Query.Close;
  Query.CommandType := cmdStoredProc;
  Query.CommandText := StoredProc;
  Query.Parameters.Clear;
  For i := VarArrayLowBound (Params, 1) To VarArrayHighBound (Params, 1) Do
  Begin
    Query.Parameters.CreateParameter(Params[i][0], Params[i][1],
Params[i][2], Params [i][3], Null);
    Query.Parameters.ParamByName(Params[i][0]).Value := Params[i][4];
  End;
// Workaround for Null Result
  SQLCommand.CommandText := StoredProc;
  SQLCommand.CommandType := cmdStoredProc;
  SQLCommand.Parameters.Assign(Query.Parameters);
  Try
    Query.Recordset := SQLCommand.Execute;
  Except
    Query.Recordset := Nil;
    raise;
  End;
end;
---------------------------------------------------------------------------- 

---
Quantcast