Monday, August 3, 2015

sys_refcursor to dynamic record/table type

DECLARE
   l_refcursor                          SYS_REFCURSOR;
   l_cols                        NUMBER;
   l_desc                        DBMS_SQL.desc_tab;
   l_curs                        INTEGER;
   l_varchar                     VARCHAR2(4000);

   FUNCTION xx_ref_cursor
      RETURN SYS_REFCURSOR
   IS
      l_refcursor                   SYS_REFCURSOR;
   BEGIN
      OPEN l_refcursor FOR
         SELECT inventory_item_id, segment1, description
           FROM mtl_system_items_b
          WHERE ROWNUM <= 10;

      RETURN l_refcursor;
   END;
BEGIN
   l_refcursor := xx_ref_cursor;
   l_curs                                              := DBMS_SQL.to_cursor_number( l_refcursor);
   DBMS_SQL.describe_columns(
      c                               => l_curs
    , col_cnt                         => l_cols
    , desc_t                          => l_desc);

   FOR i IN 1 .. l_cols
   LOOP
      DBMS_SQL.define_column(
         l_curs
       , i
       , l_varchar
       , 4000);
   END LOOP;

   WHILE DBMS_SQL.fetch_rows( l_curs) > 0
   LOOP
      FOR i IN 1 .. l_cols
      LOOP
         DBMS_SQL.COLUMN_VALUE(
            l_curs
          , i
          , l_varchar);
         DBMS_OUTPUT.put_line(
               'Row Number :'
            || DBMS_SQL.last_row_count
            || ': '
            || l_desc( i).col_name
            || ' = '
            || l_varchar);
      END LOOP;
   END LOOP;

   DBMS_SQL.close_cursor( l_curs);
END;
/