Re: Using bind vars, ref cursors and dynamic SQL

andrewst wrote:

> Originally posted by Daniel Morgan
> > Assuming 8i try this:
> >
> > CREATE OR REPLACE PROCEDURE child (
> > p_NumRecs IN PLS_INTEGER,
> > p_return_cur OUT uw_type.t_ref_cursor)
> > IS
> >
> > BEGIN
> > OPEN p_return_cur FOR
> > 'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
> >
> > END child;
> > /
> >
> > Assuming 9i try this:
> >
> > 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;
> > /
> >
> >
> Surely you meant to use bind variables?
>
> OPEN p_return_cur FOR
> 'SELECT * FROM all_tables WHERE rownum <= :n' USING p_NumRecs;
>
> --
> Posted via http://dbforums.com

Of course. Theses were just the simplest demos of returning REF CURSORs I could find.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)