I am creating a stored procedure and there is an error that no matter what I do, I cannot get rid of. Here is the procedure (variable names are modified):
-- Code
create or replace
PROCEDURE SP1(
startdate IN TIMESTAMP,
enddate IN TIMESTAMP
)
IS
BEGIN
SELECT -- line 10
offi.office_loc "Office Name",
stored_func1(startdate, enddate, 1, rep.office_id) Chinese,
Stored_Func1(startdate, enddate, 2, rep.office_id) Russian
FROM reporttbl1 rep
right outer Join offices offi on (offi.Office_ID = rep.office_id)
left outer join languages lng on (rep.language_id = lng.language_id)
WHERE rep.date between to_date(startdate,'DD-MON-YY') and to_date(enddate,'DD-MON-YY')
GROUP BY offi.Office_Loc
ORDER BY offi.Office_id;
END;
-- end Code
and there is a PLS-00428 error at line 10: an INTO clause is expected in SELECT statement.
So I decide to store "Chinese" and "Russian" columns into variables, although I do not want to do so.
-- Code
create or replace
PROCEDURE SP1(
startdate IN TIMESTAMP,
enddate IN TIMESTAMP
)
IS
declare Chinese Number; -- line 7
Russian Number;
BEGIN
SELECT -- line 10
offi.office_loc "Office Name",
stored_func1(startdate, enddate, 1, rep.office_id) INTO Chinese,
Stored_Func1(startdate, enddate, 2, rep.office_id) INTO Russian -- line 13
FROM reporttbl1 rep
right outer Join offices offi on (offi.Office_ID = rep.office_id) -- line 15
left outer join languages lng on (rep.language_id = lng.language_id)
WHERE rep.date between to_date(startdate,'DD-MON-YY') and to_date(enddate,'DD-MON-YY')
GROUP BY offi.Office_Loc
ORDER BY offi.Office_id;
END;
-- Code
and this time I got PLS-00103 errors at line 7, 13 and 15. What should I do? I am relatively new to Oracle so please help me. Thank you.