Board index » delphi » How to execute Stored Proc without using TStoredProc?

2003-10-28 04:47:23 PM
delphi232
Hi,
I have a problem with BDE+Oracle : the TStoredProc component I use would not
execute the stored proc on the server.
The error is "Invalid numeric value", and I have no idea (and there is no
way to determine) which parameter causes it.
So I decided to abandon TStoredProc component and try using something else.
But apparently, it is not possible to call stored proc using TQuery! What
else can be done?
The procedure works on the server, I tested.
The code is below, for reference.
-------------------------------- Stored procedure ----------------------
CREATE OR REPLACE FUNCTION kiw_imt_ins_proc
(p_1 kiw_imt.werkverz_nr%TYPE
,p_2 kiw_imt.taaknummer%TYPE
,p_3 kiw_imt.regelnr%TYPE
,p_4 kiw_imt.regel%TYPE
,p_object INTEGER
,p_h_trans_van DATE
,p_h_geldig_van DATE
,p_h_gebruiker VARCHAR2
,p_h_actief CHAR
,vv_result OUT NUMBER
,vv_sqlerror OUT NUMBER
,vv_isamerror OUT NUMBER
,vv_errordata OUT VARCHAR2
)
RETURN NUMBER IS
BEGIN
vv_result := 1;
SELECT kiw_imt_seq.NEXTVAL INTO vv_result FROM DUAL;
INSERT
INTO kiw_imt
(oid
,werkverz_nr
,taaknummer
,regelnr
,regel
,object
,h_trans_van
,h_geldig_van
,h_gebruiker
,h_actief
)
VALUES
(vv_result
,p_1
,p_2
,p_3
,p_4
,p_object
,p_h_trans_van
,p_h_geldig_van
,p_h_gebruiker
,p_h_actief
)
;
UPDATE kiw_imt
SET object = vv_result
WHERE oid = vv_result;
vv_isamerror := 0;
vv_sqlerror := 0;
vv_errordata :='';
RETURN vv_result;
EXCEPTION
WHEN OTHERS THEN
vv_sqlerror := SQLCODE;
vv_errordata := SQLERRM;
vv_result:= -1;
RETURN -1;
END kiw_imt_ins_proc;
/
GRANT EXECUTE ON kiw_imt_ins_proc TO PUBLIC;
----------------------------------------- Delphi call ----------------------
storProc.DatabaseName := cbxDatabase.Text;
storProc.StoredProcName := 'KDBA.KIW_IMT_INS_PROC';
storProc.Params.Clear;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_1';
par.AsCurrency := 0;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_2';
par.AsCurrency := 0;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_3';
par.AsFloat := 0;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_4';
par.AsString := 'aaa';
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_object';
par.AsCurrency := 0;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_h_trans_van';
par.AsDate := StrToDate('1-1-1900');
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_h_geldig_van';
par.AsDate := StrToDate('1-1-1900');
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_h_gebruiker';
par.AsString := inspect.currentUser.UserName;
par := TParam.Create(storProc.Params, ptInput);
par.Name := 'p_h_actief';
par.AsString := ' ';
par := TParam.Create(storProc.Params, ptOutput);
par.Name := 'vv_result';
par.DataType := ftInteger;
par := TParam.Create(storProc.Params, ptOutput);
par.Name := 'vv_sqlerror';
par.DataType := ftInteger;
par := TParam.Create(storProc.Params, ptOutput);
par.Name := 'vv_isamerror';
par.DataType := ftInteger;
par := TParam.Create(storProc.Params, ptOutput);
par.Name := 'vv_errordata';
par.DataType := ftString;
par := TParam.Create(storProc.Params, ptResult);
par.Name := 'result';
par.DataType := ftInteger;
storProc.Prepare;
storProc.ExecProc;
--------------------------------------- Error message -------------------
Key violation.
[Oracle][ODBC][Ora]ORA-04043: object "KDBA" does not exist
General SQL error.
[Oracle][ODBC]Numeric value out of range.
---------------------------
OK
---------------------------
 

One obvious thing is that you create it as "kiw_imt_ins_proc", but execute
it as "KDBA.KIW_IMT_INS_PROC", having different owner and different case.
Also, first error is 'object "KDBA" does not exist'.
--
Robert Cerny
DelphiShaman
"andru123" <andru123 at hotmail dot com>writes

Any other combination does not work. And this one does.
I managed to call the similar procedure in such way. But that procedure did
not ask for parameters.
The message 'object "KDBA" does not exist', IMHO is an Oracle bug and is
irrelevant.
But, returning back to the question,
How to execute Stored Proc without using TStoredProc?
Is there any way???????

"andru123" <andru123 at hotmail dot com>writes
don't need help, if it does.
Anyway, I don't think you tried all possible combinations.
owner, ie "KDBA" or in uppercase or....
--
Robert Cerny
DelphiShaman
 

---------------------------
Invalid use of keyword.
Token: CALL
Line Number: 1.
---------------------------
OK
---------------------------
 

"andru123" <andru123 at hotmail dot com>writes
result set, not TQuery.Open
Good luck,
krf
 

I do:
qryTest.SQL.Text :=
'CALL KIW_IMT_INS_PROC (0, 1, 1, ''aaa'', 10, ' +
' ''01-01-2002'', ' +
' ''01-01-2002'', ''aaa'', '''', ' +
' :vv_result, :vv_sqlerror, :vv_isamerror, :vv_errordata )';
qryTest.Prepare;
qryTest.ExecSQL;
exit;
Does not work. It says :
---------------------------
Invalid use of keyword.
Token: CALL
Line Number: 1.
---------------------------
OK
---------------------------

- On the Prepare or ExecSQL?
- Are you sure you have the TQuery.DatabaseName set?
- What version of Delphi/BDE are you using?
- Have you applied all known patches to Delphi/BDE?
Robert C is the formost expert in this group on Delphi/Oracle (he'll need to
add his 2 cents), so other than simple stuff (I'm more familar with MS SQL) I
have no idea. Since the BDE has been depreciated (no longer supported), I'd
look at the many other BDE alternatives for Oracle.
Good luck,
krf
"andru123" <andru123 at hotmail dot com>writes

Thanks a lot,
I forgot to set the qryTest.DatabaseName.
"Kevin Frevert" <XXXX@XXXXX.COM>schreef in bericht