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 -> Re: Problem calling pl/sql procedure from sql query

Re: Problem calling pl/sql procedure from sql query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 13 Jan 2004 17:57:48 +0100
Message-ID: <40042339$0$1147$636a55ce@news.free.fr>

"Paul Manson" <paul.manson_at_liv-coll.ac.uk> a écrit dans le message de news:c6074436.0401130818.768462db_at_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;
> /

Try a function returning a type and table(type) in from clause. If you return many rows, have a look at pipelined functions and pipe row statement.

Regards
Michel Cadot Received on Tue Jan 13 2004 - 10:57:48 CST

Original text of this message

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