Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Problem calling pl/sql procedure from sql query

Problem calling pl/sql procedure from sql query

From: Paul Manson <paul.manson_at_liv-coll.ac.uk>
Date: 13 Jan 2004 08:18:35 -0800
Message-ID: <c6074436.0401130818.768462db@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US