DATE problem with ORACLE stored procedure
Yvan Schwab
I have a problem with a stored procedure in an Oracle database.
I believe that the problem is related to a parameter with data type of DATE.
The stored procedure as the following signature:
PROCEDURE p_delstat (p_dateeffacement DATE, p_delcumul INTEGER)
AS
BEGIN
...
END;
The stored procedure component is defined in a data module as follow:
object PrcDelStat: TStoredProc
DatabaseName = 'StatDatabase'
StoredProcName = 'P_DELSTAT'
Left = 88
Top = 176
ParamData = <
item
DataType = ftDateTime
Name = 'DATEEFFACEMENT'
ParamType = ptInput
end
item
DataType = ftInteger
Name = 'DELCUMUL'
ParamType = ptInput
end>
end
The delphi code which calls the stored procedure looks like:
dmStat.PrcDelStat.parambyname('DateEffacement').AsDateTime:=StrToDateTime(cE
ffDate.Text);
dmStat.PrcDelStat.parambyname('DelCumul').AsInteger:=1
dmStat.PrcDelStat.ExecProc;
dmStat.PrcDelStat.Close;
The trouble is that I get an error message.
When I monitor this call with the SLQ Monitor, I get the following trace:
1 13:15:41 SQL Transact: ORACLE - Set autocommit on/off
2 13:15:41 SQL Prepare: ORACLE - BEGIN "P_DELSTAT" (DATEEFFACEMENT =>
:DATEEFFACEMENT,DELCUMUL => :DELCUMUL); END;
3 13:15:41 SQL Vendor: OCI 8 - OCIHandleAlloc
4 13:15:41 SQL Vendor: OCI 8 - OCIHandleAlloc
5 13:15:41 SQL Vendor: OCI 8 - OCIStmtPrepare
6 13:15:41 SQL Vendor: OCI 8 - OCIAttrGet
7 13:15:41 SQL Vendor: OCI 8 - OCIBindByPos
8 13:15:41 SQL Vendor: OCI 8 - OCIBindByPos
9 13:15:41 SQL Vendor: OCI 8 - OCIBindByPos
10 13:15:41 SQL Misc: ORACLE - Set stored procedure on or off
11 13:15:41 SQL Data In: ORACLE - Param = 1, Name = DATEEFFACEMENT,
Type = fldTIMESTAMP, Precision = 0, Scale = 0, Data =
12/19/1999 0:0:0:0
12 13:15:41 SQL Vendor: OCI 8 - OCIBindByName
13 13:15:41 SQL Data In: ORACLE - Param = 2, Name = DELCUMUL,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 1.000000
14 13:15:41 SQL Vendor: OCI 8 - OCIBindByName
15 13:15:41 SQL Execute: ORACLE - BEGIN "P_DELSTAT" (DATEEFFACEMENT =>
:DATEEFFACEMENT,DELCUMUL => :DELCUMUL); END;
16 13:15:41 SQL Vendor: OCI 8 - OCIStmtExecute
17 13:15:41 SQL Vendor: OCI 8 - OCIErrorGet
18 13:15:41 SQL Error: ORACLE - ORA-06550: Ligne 1, colonne 7 :
PLS-00306: numéro ou types d'arguments erronés dans appel à 'P_DELSTAT'
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored
19 13:15:41 SQL Vendor: OCI 8 - OCIErrorGet
20 13:15:41 SQL Error: ORACLE - Unmapped SQL Error Code: 6550
21 13:15:41 SQL Stmt: ORACLE - Reset
22 13:15:41 SQL Stmt: ORACLE - Close
Any clue on what's wrong with this stored procedure and/or call ?
Thanks in advance
Andre
PS: Using Delhi4 and Oracle 8.1.5 Window/NT
--
Sadamel
Bureau d'étude et de dévelopement
Andre Rutti
tec...@sadamel.ch
Rue du Collège 73
CH-2300 La Chaux-de-Fonds
Tel +41 (0)32 968 0770
Fax +41 (0)32 968 0885
Marc Scheuner
wrote:
>PROCEDURE p_delstat (p_dateeffacement DATE, p_delcumul INTEGER)
If your parameters are called "p_dateeffacement" and "p_delcumul" in
Oracle, make sure you use the EXACT same names (including
capitalization!) in Delphi !
> ParamData = <
> item
> DataType = ftDateTime
> Name = 'DATEEFFACEMENT'
Replace name with "p_dateeffacement".
> item
> DataType = ftInteger
> Name = 'DELCUMUL'
Replace name with p_delcumul.
Marc
--------------------------------------------------------------------------
Marc Scheuner Berner Versicherungen, Dept. ISV
May the Source be With You Laupenstrasse 27
marc.s...@berner.ch CH-3001 BERNE, Switzerland
--------------------------------------------------------------------------