Oracle cusor return result and get value by dynamic column name

Question

As tittle, I want to get value by column name as paramater in cursor result.

I could not find the answer after searching a long time,Could anybody give some help?

--common usage
BEGIN
    for rowValue in (select code, name from tableA)
    LOOP
     -- rowValue.code  rowValue.name
    END LOOP;
END


BEGIN
    FOR rowValue IN (select * from tableA)
    LOOP
        -- tableB will keep some column's names about talbeA
        FOR columnNames IN (select name from tableB)
        LOOP
         -- want to use like:  rowValue.(columnNames.name)
        END LOOP;
    END LOOP;
END

Answer 1

You can use DBMS_SQL, like this:

DECLARE
  l_cursor                INTEGER;
  l_column_count          INTEGER;
  l_column_descriptions   SYS.DBMS_SQL.desc_tab;
  l_column_value          VARCHAR2 (4000);
  l_status                INTEGER;

  TYPE col_map_tab_type IS TABLE OF INTEGER
    INDEX BY VARCHAR2 (30);

  col_map_tab             col_map_tab_type;
BEGIN
  l_cursor   := sys.DBMS_SQL.open_cursor;

  -- parse SQL
  sys.DBMS_SQL.parse (c => l_cursor, statement => 'SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 10', language_flag => sys.DBMS_SQL.native);

  -- Describe columns
  sys.DBMS_SQL.describe_columns (c => l_cursor, col_cnt => l_column_count, desc_t => l_column_descriptions);

  FOR i IN 1 .. l_column_count LOOP
    col_map_tab (l_column_descriptions (i).col_name)   := i;
    DBMS_SQL.define_column (l_cursor,
                            i,
                            l_column_value,
                            4000);
  END LOOP;

  l_status   := sys.DBMS_SQL.execute (l_cursor);

  WHILE (sys.DBMS_SQL.fetch_rows (l_cursor) > 0) LOOP
    FOR column_names IN (SELECT column_name
                         FROM   dba_tab_columns
                         WHERE  table_name = 'DBA_OBJECTS'
                         AND    column_name LIKE 'O%') LOOP
      DBMS_SQL.COLUMN_VALUE (l_cursor, col_map_tab (column_names.column_name), l_column_value);
      DBMS_OUTPUT.put_line (column_names.column_name || ' = ' || l_column_value);
    END LOOP;
  END LOOP;

  sys.DBMS_SQL.close_cursor (l_cursor);
END;