Re: EXECUTE IMMEDIATE and SELECT in Stored Proc?

Daniel,

Thanks!

John

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1064877141.478835_at_yasure>...
> John Puopolo wrote:
>
> >Daniel,
> >
> >Thanks. Do you have an sample code (PL/SQL) that actually works? The
> >docs are difficult to follow.
> >
> >Any help appreciated. Thanks.
> >
> >John
> >
> >
> >Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1064702006.991570_at_yasure>...
> >
> >
> >>John Puopolo wrote:
> >>
> >>
> >>
> >>>All,
> >>>
> >>>I am trying to execute a stored proc. that takes as a parameter a
> >>>'where' clause.
> >>>
> >>>I am formulating the entire SQL statement in code, e.g.,
> >>>(assume all variables declared properly)
> >>>
> >>>s := 'open p_rs for select col1 from table1 where ' || p_where_clause;
> >>>
> >>>Is this this right way to do it? I've tried terminating the statement
> >>>w/ a semicolon, tried taking the semicolon away, tried to put p_rs on
> >>>the end, etc.
> >>>
> >>>Is there *any way* to write a dynamic sql statement that returns N
> >>>records back this way?
> >>>
> >>>Many thanks,
> >>>John
> >>>
> >>>
> >>>
> >>>
> >>Go to tahiti.oracle.com and look for the OPEN <ref_cursor> FOR version
> >>of dynamic SQL.
> >>
> >>
> Assuming you have 9i use this demo. If you have 8i you will need to
> declare your REF CURSORs as a TYPE.
>
> CREATE OR REPLACE PROCEDURE child (
> p_NumRecs IN PLS_INTEGER,
> p_return_cur OUT SYS_REFCURSOR)
> IS
>
> BEGIN
> OPEN p_return_cur FOR
> 'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
>
> END child;
> /
>
>
>
>
> CREATE OR REPLACE PROCEDURE parent (
> pNumRecs VARCHAR2)
> IS
>
> p_retcur SYS_REFCURSOR;
> at_rec all_tables%ROWTYPE;
>
> BEGIN
> child(pNumRecs, p_retcur);
>
> FOR i IN 1 .. pNumRecs
> LOOP
> FETCH p_retcur
> INTO at_rec;
>
> DBMS_OUTPUT.PUT_LINE(at_rec.table_name ||
> ' - ' || at_rec.tablespace_name ||
> ' - ' || TO_CHAR(at_rec.initial_extent) ||
> ' - ' || TO_CHAR(at_rec.next_extent));
> END LOOP;
>
> END parent;
> /
>
>
> To test:
>
> SQL> exec parent(5)
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>
> --
Received on Wed Oct 01 2003 - 14:56:47 CDT