Re: Dynamic cursor definition

Jan

You will need to use dynamic SQL for this. See Native Dynamic SQL and ref cursors in the docs for details but I've added a small example below. There will be a cost associated with this extra flexibility however, in that every execution of the SQL in the cursor will cause a soft parse. You wouldn't get this with static SQL (which would parse once and then execute many).

Using NDS...

CREATE PROCEDURE dyn_cur (order_by_in IN LONG) AS

   TYPE typ_ref_cursor IS REF CURSOR;
   rc typ_ref_cursor;

   v_sql LONG := 'SELECT table_name, pct_used FROM user_tables ';    v_order LONG := order_by_in;

   v_tab user_tables.table_name%TYPE;
   v_pct user_tables.pct_used%TYPE;

BEGIN    /* Open the ref cursor for the dynamic SQL... */    OPEN rc FOR v_sql || v_order;
   LOOP
   FETCH rc INTO v_tab, v_pct;
   EXIT WHEN rc%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(v_tab||'....'||to_char(v_pct));    END LOOP;
   CLOSE rc;

END;
/

BEGIN
dyn_cur('ORDER BY table_name');
dyn_cur('ORDER BY pct_used');
END;
/

Regards

Adrian

--
Posted via http://dbforums.com