How to use Dynamic Cursor in a PL/SQL

PL/SQL script to use a dynamic cursor.



CREATE OR REPLACE PROCEDURE XXX_dynamic_cursor (p_segment IN VARCHAR2)
IS
   v_sql            VARCHAR2 (2000) := NULL;
   v_segment        VARCHAR2 (50);
   l_segment_desc   VARCHAR2 (100);

   TYPE cur_typ IS REF CURSOR;

   c_seg_cur        cur_typ;
BEGIN
   v_sql :=
         'SELECT distinct ss.'
      || p_segment
      || ' , ffvl.description
             FROM XXXX_TEMP_GL ss,
                  fnd_id_flex_segments fifs,
                  fnd_flex_value_sets ffvs,
                  fnd_flex_values ffv,
                  fnd_flex_values_tl ffvl
            WHERE     ss.chart_of_accounts_id = fifs.id_flex_num
                  AND fifs.application_column_name = :p_segment AND fifs.flex_value_set_id = ffvs.flex_value_set_id
                  AND ffvs.flex_value_set_id = ffv.flex_value_set_id
                  AND ffv.flex_value_id = ffvl.flex_value_id
                  AND ss.'
      || p_segment
      || '   = ffv.flex_value
                  order by 1';

   OPEN c_seg_cur FOR v_sql USING p_segment;

   LOOP
      FETCH c_seg_cur
      INTO v_segment, l_segment_desc;

      EXIT WHEN c_seg_cur%NOTFOUND;
      DBMS_OUTPUT.put_line (v_segment || l_segment_desc);
   -- process row here
   END LOOP;

   CLOSE c_seg_cur;
END;
/