Tenho uma procedure que roda no Oracle.
Quando faço a chamada da Procedure pelo sqlplus funciona normalmente.
Porém qdo faço a chamada pelo delphi dá erro de conversao de caracter em numérico.
no delphi faço assim:
StoredProc:=TSQLStoredProc.Create(nil);
with StoredProc do try
SQLConnection:=dtm.Conexao;
SQLConnection.Open;
StoredProcName:='P_RANDOMIZE';
Params.ParamByName('PID_USER').AsString := 'PLEMOS';
Params.ParamByName('PDT_H_EXEC').AsString := '15/09/2005 17:26:17';
Params.ParamByName('PCRF').AsInteger := 1;
Params.ParamByName('PCENTRO').AsInteger := 1;
Params.ParamByName('PSEQ1').AsInteger := 2;
Params.ParamByName('PSEQ2').AsInteger := 3;
Params.ParamByName('PSEQ3').AsInteger := 1;
ExecProc;
finally
StoredProc.Free;
end;
A Procedure é assim:
CREATE OR REPLACE procedure P_RANDOMIZE
(
Pid_user in varchar2,
Pdt_h_exec in varchar2,
Pcrf in number,
Pcentro in number,
Pseq1 in number,
Pseq2 in number,
Pseq3 in number)
AS
wctrl number;
Wseqr number;
Wseqx number;
Wtotp number;
Wnprd varchar2(20);
BEGIN
LOCK TABLE randomized IN EXCLUSIVE MODE;
Wctrl := 1;
Wseqx := null;
Wseqr := null;
Wnprd := 'NOT FOUND';
SELECT COUNT(*), max(id_product)
INTO Wtotp, Wseqx
FROM randomized
WHERE id_CRF = Pcrf
AND id_centro = Pcentro;
IF Wtotp = 0 THEN
BEGIN
INSERT INTO randomized VALUES (Pcrf,Pcentro,1);
INSERT INTO randomized VALUES (Pcrf,Pcentro,2);
INSERT INTO randomized VALUES (Pcrf,Pcentro,3);
END;
ELSIF Wtotp = 1 THEN
Wctrl := 0;
END IF;
LOOP
EXIT WHEN (Wctrl > 3) OR (Wseqr IS NOT NULL);
IF Wctrl = 1 THEN
Wseqx := Pseq1;
ELSIF Wctrl = 2 THEN
Wseqx := Pseq2;
ELSIF Wctrl = 3 THEN
Wseqx := Pseq3;
END IF;
BEGIN
SELECT p.id_product, p.nm_product
INTO Wseqr, Wnprd
FROM randomized r,
product_study p
WHERE p.id_product = r.id_product
AND r.id_CRF = Pcrf
AND r.id_centro = Pcentro
AND r.id_product = Wseqx;
DELETE randomized
WHERE id_CRF = Pcrf
AND id_centro = Pcentro
AND id_product = Wseqx;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
Wctrl := Wctrl +1;
END LOOP;
INSERT INTO randomize_result VALUES (Pid_user, Pdt_h_exec, Wseqr, Wnprd);
COMMIT;
END;
/
nao estou encontrando o erro..
pode me ajudar?
Obrigada