Calling Stored procedure with date/time parameter

[awalli]

Hallo to all,
I have the following problem. With the package wizard I created an interface to a stored procedure that asks a date value as input parameter.
The call of the procedure does not function and returns a PLS-00306 as if the parameters passed are wrong or of wrong datatype.
Following the declarations:
{CALL PROCEDURE FROM DELPHI}
PkgSisIns.CreateYearPayment( ubzodsStudData.FieldByName('STUD_ID').AsInteger,
ubzodsStudData.FieldByName('REQUEST_YEAR').AsInteger,
edPaymentDate.Date,
//date(),
1,
1,
0,
4,
resultBool);

{DECLARATION IN DELPHI}

procedure TPkgSisIns.CreateYearPayment(const PStud: Variant; const PYear: Variant;
const PPayDate: TDateTime; const PDegrYear: Variant;
const PInst: Variant; const PFcFlag: Variant; const PPtyp: Variant; out WarnCode: Boolean);
begin
GetQuery;
OCPQuery.DeclareVariable('P_STUD', otFloat);
OCPQuery.SetVariable('P_STUD', PStud);
OCPQuery.DeclareVariable('P_YEAR', otFloat);
OCPQuery.SetVariable('P_YEAR', PYear);
OCPQuery.DeclareVariable('P_PAY_DATE', otDate);
// OCPQuery.DeclareVariable('P_PAY_DATE', otString);
OCPQuery.SetVariable('P_PAY_DATE', PPayDate);
OCPQuery.DeclareVariable('P_DEGR_YEAR', otFloat);
OCPQuery.SetVariable('P_DEGR_YEAR', PDegrYear);
OCPQuery.DeclareVariable('P_INST', otFloat);
OCPQuery.SetVariable('P_INST', PInst);
OCPQuery.DeclareVariable('P_FC_FLAG', otFloat);
OCPQuery.SetVariable('P_FC_FLAG', PFcFlag);
OCPQuery.DeclareVariable('P_PTYP', otFloat);
OCPQuery.SetVariable('P_PTYP', PPtyp);
OCPQuery.DeclareVariable('WARNCODE', otInteger);
// OCPQuery.SQL.Add('declare');
// OCPQuery.SQL.Add(' WARNCODE boolean;');
OCPQuery.SQL.Add('begin');
OCPQuery.SQL.Add(' "PKG_SIS_INS"."CREATE_YEAR_PAYMENT"(');
OCPQuery.SQL.Add(' _STUD,');
OCPQuery.SQL.Add(' _YEAR,');
//OCPQuery.SQL.Add(' To_date("_PAY_DATE", "dd/mm/yy"),');
//OCPQuery.SQL.Add(' sysdate, ');
OCPQuery.SQL.Add(' _PAY_DATE,');
OCPQuery.SQL.Add(' _DEGR_YEAR,');
OCPQuery.SQL.Add(' _INST,');
OCPQuery.SQL.Add(' _FC_FLAG,');
OCPQuery.SQL.Add(' _PTYP,');
OCPQuery.SQL.Add(' :WARNCODE);');
// OCPQuery.SQL.Add(' :WARNCODE := sys.diutil.bool_to_int(WARNCODE);');
OCPQuery.SQL.Add('end;');
OCPQuery.Session := dmMain.OracleSession1;
OCPQuery.Execute;
Warncode := IntToBool(OCPQuery.GetVariable('WARNCODE'));
end;

{STORED PROCEDURE}

PROCEDURE Create_Year_Payment(p_stud number, p_year number, p_pay_date date, p_degr_year number,
p_inst number, p_fc_flag number,p_PTYP number, warncode out boolean) IS
BEGIN
warncode := False;
begin
insert into year_enrolmts
(yenr_stud_id,
yenr_year,
yenr_degr_year,
yenr_fees_ok)
values
(p_stud,
to_number(to_char(sysdate, 'yyyy')),
1,
1);

pkg_sis.UPDATE_YEAR_PAYMENT(p_stud, p_year, p_pay_date, p_degr_year,
p_inst, p_fc_flag ,p_PTYP );

commit;

exception
when others then
-- raise_application_error(-201405, 'Creation of Year Payment not successfully');
INSERT_ERROR_TABLE(p_stud, 4, False);
warncode := True;
Rollback;
end;

INSERT_ERROR_TABLE(p_stud, 4, True);
END Create_Year_Payment;

THAT'S ALL for the moment.
Excuse me for the long post and thanks in advance for your responses

Alexander Walli
Free University of Bozen/Bolzano
Italy

 

[Marco Kalter]

It seems that you have modified the generated code. The WARNCODE should be a BOOLEAN in the parameter list, not an INTEGER. You have removed these 2 lines:

OCPQuery.SQL.Add('declare');
OCPQuery.SQL.Add(' WARNCODE boolean;');

And have changed these orginal lines:

OCPQuery.SQL.Add(' WARNCODE);');
OCPQuery.SQL.Add(' :WARNCODE := sys.diutil.bool_to_int(WARNCODE);');

to this:

OCPQuery.SQL.Add(' :WARNCODE);');

This causes a PLS-00306.

------------------
Marco Kalter
Allround Automations