Thursday, September 7, 2017

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

No comments:

Post a Comment

Price List Query for Item

 SELECT qph.list_header_id,        qph.name,        qph.description,        qphh.start_date_active,        qphh.currency_code,        q...