Fetch DYNAMIC/RUNTIME Table Column Name with Values

CREATE TABLE APPS.STUDENT

(

ID_T NUMBER,

NAME_T VARCHAR2(50 BYTE),

MARK_T NUMBER

)

select * from student

ID_T NAME_T MARK_T
1 chidam 100
2 div 200
3 aksh 300


PLSQL Scripts

declare

  --v1.1 added

  l_cur    number;

  l_dtbl   dbms_sql.desc_tab;

  l_cnt    number;

  l_status number;

  l_val    varchar2(200);

begin

  l_cur := dbms_sql.open_cursor;

  dbms_sql.parse(l_cur, 'SELECT * from student', dbms_sql.native);

  dbms_sql.describe_columns(l_cur, l_cnt, l_dtbl);

  for i in 1 .. l_cnt

  loop

    dbms_sql.define_column(l_cur, i, l_val, 240);

  end loop;

  l_status := dbms_sql.execute(l_cur);

  while (dbms_sql.fetch_rows(l_cur) > 0) loop

    for i in 1 .. l_cnt loop

      dbms_sql.column_value(l_cur, i, l_val);

      dbms_output.put_line(l_dtbl(i).col_name || ' --> ' || l_val);

    end loop;

  end loop;

  dbms_sql.close_cursor(l_cur);

end;

OUTPUT

ID_T --> 1
NAME_T --> chidam
MARK_T --> 100

ID_T --> 2
NAME_T --> div
MARK_T --> 200

ID_T --> 3
NAME_T --> aksh
MARK_T --> 300