Below given code will give all data from all the tables present in HR user and i want to use the same code to filter the data from all the tables by using ID columns.Can i do that with same code?
EXEC to_check_data('employees');
EXEC to_check_data('departments');
EXEC to_check_data('countries');
EXEC to_check_data('locations');
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE OR REPLACE PROCEDURE to_check_data (
p_table IN VARCHAR2)
IS
TYPE t_list IS TABLE OF user_tab_columns%rowtype INDEX BY PLS_INTEGER;
v_array t_list;
to_store_column VARCHAR2(32767);
v_number NUMBER;
v_varchar VARCHAR2(32767);
v_date DATE;
v_cursor PLS_INTEGER;
v_count PLS_INTEGER;
v_column_name VARCHAR2(32767);
BEGIN
SELECT
*
BULK COLLECT
INTO v_array
FROM
user_tab_columns
WHERE
table_name = upper(p_table);
to_store_column := v_array(1).column_name;
FOR i IN 2..v_array.count LOOP
to_store_column := to_store_column
|| ','
|| v_array(i).column_name;
END LOOP;
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, 'select '
|| to_store_column
|| ' from '
|| p_table, dbms_sql.native);
FOR idx IN 1..v_array.count LOOP
IF v_array(idx).data_type = 'NUMBER' THEN
dbms_sql.define_column(v_cursor, idx, 1);
ELSIF v_array(idx).data_type IN ( 'VARCHAR2', 'VARCHAR', 'CHAR' ) THEN
dbms_sql.define_column(v_cursor, idx, 'Dumy Text', v_array(idx).char_length);
ELSIF v_array(idx).data_type = 'DATE' THEN
dbms_sql.define_column(v_cursor, idx, sysdate);
END IF;
v_column_name := v_column_name
|| ' '
|| rpad(v_array(idx).column_name, 20);
END LOOP;
dbms_output.put_line(v_column_name);
v_number := dbms_sql.execute(v_cursor);
WHILE dbms_sql.fetch_rows(v_cursor) > 0 LOOP
v_column_name := NULL;
FOR t IN 1..v_array.count LOOP
IF v_array(t).data_type = 'NUMBER' THEN
dbms_sql.column_value(v_cursor, t, v_number);
v_column_name := v_column_name
|| ' '
|| rpad(nvl(to_char(v_number), ' '), 20);
ELSIF v_array(t).data_type IN ( 'VARCHAR2', 'VARCHAR', 'CHAR' ) THEN
dbms_sql.column_value(v_cursor, t, v_varchar);
v_column_name := v_column_name
|| ' '
|| rpad(nvl(to_char(v_varchar), ' '), 20);
ELSIF v_array(t).data_type = 'DATE' THEN
dbms_sql.column_value(v_cursor, t, v_date);
v_column_name := v_column_name
|| ' '
|| rpad(nvl(to_char(v_date), ' '), 20);
END IF;
END LOOP;
dbms_output.put_line(v_column_name);
END LOOP;
END;