Re: Using bind vars, ref cursors and dynamic SQL

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