Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Problem calling pl/sql procedure from sql query
I'm brand new to pl/sql and having problems! I think I've created a
stored procedure OK (it compiles OK, with no errors) but I don't know
how to call the OUT values into an SQL query.
Can anyone help? I need to know if the procedure script is OK and how to call p_ddate, p_tstat, and p_pstat values into a query.
Thanks in advance.
CREATE OR REPLACE procedure proc_aut_ddate(p_person_code IN NUMBER, p_due_date IN DATE, p_ddate IN OUT DATE, p_tstat IN OUT VARCHAR2, p_pstat IN OUT VARCHAR2) IS
CURSOR cur_autddate IS
SELECT
due_date ddate,
timetable_status tstat,
nvl(payment_status, 'NONE') pstat
FROM lcc_weekly_payment_status wps
Where person_code = p_person_code
and due_date = p_due_date
and due_date between '05-SEP-03' and '19-DEC-03';
BEGIN
p_ddate := null; p_tstat := null; p_pstat := null;
OPEN cur_autddate;
LOOP
FETCH cur_autddate INTO p_ddate, p_tstat, p_pstat;
EXIT when cur_autddate %NOTFOUND;
END LOOP;
CLOSE cur_autddate;
END;
/
Received on Tue Jan 13 2004 - 10:18:35 CST
![]() |
![]() |