Home » Developer & Programmer » Forms » calling store procedure in Oracle9i
calling store procedure in Oracle9i [message #80472] Fri, 27 September 2002 06:30 Go to next message
chhan
Messages: 19
Registered: June 2002
Junior Member
Hi All,

I have written a store procedure which calculate the day fine if owner is late with their payment. below is the code, which i execute in SQL *PLUS and got the message compile with no error.

Create or replace procedure DayFine1 (FID number, DateIn date) as
Day_FineTemp number;
Day_Fine number;
DatePaid Date;
begin
if DatePaid IS NULL then
Day_FineTemp := SysDate - DateIn;
if day_FineTemp >14 then
Day_Fine := 14;
else Day_Fine := Day_FineTemp;
end if;
end if;
Update tblFine
set DayFine=Day_Fine
where FineID = FID;
end;

i test the above in SQL PLUS, just to make sure it work by using:

execute DayFine1('19', '16-feb-2002');

I got the expected result, so i know my code is ok.

The problem is i want to call this procedure in my form, so i have created a button and in the WHEN-BUTTON-PRESSED TRIGGER, i entered the following:

BEGIN
:DayFine:=DayFine1(:tblEnterinto.FineID, :tblEnterInto.DateIn);
Execute_query;
select VehicleID
into :tblFine.VehicleID
from tblFine
where tblfine.VehicleID= :tblEnterInto.VehicleID;
Execute_query;

end;


The error i get when i pressed compile is:

NO FUNCTION WITH NAME DAYFINE1 EXISTS IN THIS SCOPE.

What have i done wrong? My store procedure IS DEFINITY CALLED DAYFINE1.

can anyone help.

thanks

chhan
Re: calling store procedure in Oracle9i [message #80473 is a reply to message #80472] Fri, 27 September 2002 12:02 Go to previous messageGo to next message
visitor
Messages: 1
Registered: September 2002
Junior Member
u have written a procedure..not a FUNCTION..procedures won't return any value..
:DayFine:=DayFine1(:tblEnterinto.FineID, :tblEnterInto.DateIn);
This portion of ur code is wrong.
Call the procedure like this
DayFine1(:tblEnterinto.FineID, :tblEnterInto.DateIn);
or make DayFine1 a FUNCTION..
Re: calling store procedure in Oracle9i [message #80476 is a reply to message #80472] Sat, 28 September 2002 10:59 Go to previous message
Julie
Messages: 98
Registered: February 2002
Member
Chhan,
I think if you make the procedure part of a package and when you call it you use the
package.procedure(arg1, arg2); method of referencing it, you won't get the scope message.
Julie
Previous Topic: Re: Diff between forms6 and 6i
Next Topic: commit in forms- ORA-00001
Goto Forum:
  


Current Time: Sat Mar 02 11:12:43 CST 2024