Dynamically Updating Fetching Editing Printing Table Column/Column Names Using Oracle/ref cursor
Introduction:
In this article i am going to explain how to dynamically updating fetching oracle dynamic table columns.
Main:
declare
t_tablename CHAR := dyn_20101980; //Just Pass yours dynamic table
result_cv IN OUT ref_cursor;
t_column_name VARCHAR2(100);
t_column_list VARCHAR2(100);
t_query VARCHAR2(100);
CURSOR col_cur
IS
SELECT column_name
FROM all_tab_columns
WHERE table_name = t_tablename;
BEGIN
OPEN col_cur;
LOOP
FETCH col_cur
INTO t_column_name;
EXIT WHEN col_cur%NOTFOUND;
IF t_column_name = 'SALARY_DETAILS'
THEN
t_column_name := (|| t_column_name || * 100/10')SALARY'; //Just foramtting headers and values
END IF;
IF t_column_list IS NULL
THEN
t_column_list := 'SELECT' || t_column_name;
ELSE
t_column_list := t_column_list || ',' || t_column_name;
END IF;
END LOOP;
t_column_list := t_column_list || 'FROM' || t_tablename;
OPEN result_cv FOR t_column_list;
END;
declare t_tablename CHAR := dyn_20101980; //Just Pass yours dynamic table result_cv IN OUT ref_cursor; t_column_name VARCHAR2(100); t_column_list VARCHAR2(100); t_query VARCHAR2(100); CURSOR col_cur IS SELECT column_name FROM all_tab_columns WHERE table_name = t_tablename; BEGIN OPEN col_cur; LOOP FETCH col_cur INTO t_column_name; EXIT WHEN col_cur%NOTFOUND; IF t_column_name = 'SALARY_DETAILS' THEN t_column_name := (|| t_column_name || * 100/10')SALARY'; //Just foramtting headers and values END IF; IF t_column_list IS NULL THEN t_column_list := 'SELECT' || t_column_name; ELSE t_column_list := t_column_list || ',' || t_column_name; END IF; END LOOP; t_column_list := t_column_list || 'FROM' || t_tablename; OPEN result_cv FOR t_column_list; END; |
Conclusion:
Hope this helps,
Happy Coding.