how to dynamically loop over all column values in a table where table and column name are variables

Question

so far I have the code below to loop over all table and column names

    for table_names in (select table_name from all_tables) loop
        l_table_name := table_names.table_name;
            for col_names in (select column_name from all_tab_cols where table_name = l_table_name) loop
                l_col_name := col_names.column_name;

now I want to loop over all values in a column

                for vals in (select l_col_name from l_table_name) loop
                    dbms_output.put_line(1);
                end loop;

error

PL/SQL: ORA-00942: table or view does not exist

Answer 1

You could do some dynamic SQL but to be honest, I'm not sure what benefit you'll get ...but anyway, here is something to get you started

SQL> declare
  2    rc sys_refcursor;
  3    l_date date;
  4    l_timestamp timestamp;
  5    l_number number;
  6    l_string varchar2(4000);
  7  begin
  8   for table_names in (select table_name from user_tables) loop
  9              for col_names in (select column_name , data_type from all_tab_cols where table_name = table_names.table_name) loop
 10                  open rc for 'select '||col_names.column_name||' from '||table_names.table_name;
 11                  loop
 12                    if col_names.data_type = 'DATE' then
 13                      fetch rc into l_date;
 14                      dbms_output.put_line(table_names.table_name||','||col_names.column_name||','||l_date);
 15                    elsif col_names.data_type like 'TIMESTAMP%' then
 16                      fetch rc into l_timestamp;
 17                      dbms_output.put_line(table_names.table_name||','||col_names.column_name||','||l_timestamp);
 18                    elsif col_names.data_type like 'NUMBER%' then
 19                      fetch rc into l_number;
 20                      dbms_output.put_line(table_names.table_name||','||col_names.column_name||','||l_number);
 21                    else
 22                      fetch rc into l_string;
 23                      dbms_output.put_line(table_names.table_name||','||col_names.column_name||','||l_string);
 24                    end if;
 25                    exit when rc%notfound;
 26                  end loop;
 27          end loop;
 28   end loop;
 29  end;
 30  /
EMP,EMPNO,7369
EMP,EMPNO,7499
EMP,EMPNO,7521
EMP,EMPNO,7566
EMP,EMPNO,7654
EMP,EMPNO,7698
EMP,EMPNO,7782
EMP,EMPNO,7788
EMP,EMPNO,7839
EMP,EMPNO,7844
EMP,EMPNO,7876
EMP,EMPNO,7900
EMP,EMPNO,7902
EMP,EMPNO,7934
EMP,EMPNO,7934
EMP,ENAME,SMITH
EMP,ENAME,ALLEN
EMP,ENAME,WARD
EMP,ENAME,JONES
EMP,ENAME,MARTIN
EMP,ENAME,BLAKE
EMP,ENAME,CLARK
EMP,ENAME,SCOTT
EMP,ENAME,KING
EMP,ENAME,TURNER
EMP,ENAME,ADAMS
EMP,ENAME,JAMES
EMP,ENAME,FORD
EMP,ENAME,MILLER
EMP,ENAME,MILLER
EMP,JOB,CLERK
EMP,JOB,SALESMAN
EMP,JOB,SALESMAN
EMP,JOB,MANAGER
EMP,JOB,SALESMAN
EMP,JOB,MANAGER
EMP,JOB,MANAGER
EMP,JOB,ANALYST
EMP,JOB,PRESIDENT
EMP,JOB,SALESMAN
EMP,JOB,CLERK
EMP,JOB,CLERK
EMP,JOB,ANALYST
EMP,JOB,CLERK
EMP,JOB,CLERK
EMP,MGR,7902
EMP,MGR,7698
EMP,MGR,7698
EMP,MGR,7839
EMP,MGR,7698
EMP,MGR,7839
EMP,MGR,7839
EMP,MGR,7566
EMP,MGR,
EMP,MGR,7698
EMP,MGR,7788
EMP,MGR,7698
EMP,MGR,7566
EMP,MGR,7782
EMP,MGR,7782
EMP,HIREDATE,17-DEC-80
EMP,HIREDATE,20-FEB-81
EMP,HIREDATE,22-FEB-81
EMP,HIREDATE,02-APR-81
EMP,HIREDATE,28-SEP-81
EMP,HIREDATE,01-MAY-81
EMP,HIREDATE,09-JUN-81
EMP,HIREDATE,09-DEC-82
EMP,HIREDATE,17-NOV-81
EMP,HIREDATE,08-SEP-81
EMP,HIREDATE,12-JAN-83
EMP,HIREDATE,03-DEC-81
EMP,HIREDATE,03-DEC-81
EMP,HIREDATE,23-JAN-82
EMP,HIREDATE,23-JAN-82
EMP,SAL,800
EMP,SAL,1600
EMP,SAL,1250
EMP,SAL,2975
EMP,SAL,1250
EMP,SAL,2850
EMP,SAL,2450
EMP,SAL,3000
EMP,SAL,5000
EMP,SAL,1500
EMP,SAL,1100
EMP,SAL,950
EMP,SAL,3000
EMP,SAL,1300
EMP,SAL,1300
EMP,COMM,
EMP,COMM,300
EMP,COMM,500
EMP,COMM,
EMP,COMM,1400
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,COMM,
EMP,DEPTNO,20
EMP,DEPTNO,30
EMP,DEPTNO,30
EMP,DEPTNO,20
EMP,DEPTNO,30
EMP,DEPTNO,30
EMP,DEPTNO,10
EMP,DEPTNO,20
EMP,DEPTNO,10
EMP,DEPTNO,30
EMP,DEPTNO,20
EMP,DEPTNO,30
EMP,DEPTNO,20
EMP,DEPTNO,10
EMP,DEPTNO,10
BONUS,ENAME,CLERK
BONUS,JOB,CLERK
BONUS,SAL,10
BONUS,COMM,10
DEPT,DEPTNO,10
DEPT,DEPTNO,20
DEPT,DEPTNO,30
DEPT,DEPTNO,40
DEPT,DEPTNO,40
DEPT,DNAME,ACCOUNTING
DEPT,DNAME,RESEARCH
DEPT,DNAME,SALES
DEPT,DNAME,OPERATIONS
DEPT,DNAME,OPERATIONS
DEPT,LOC,NEW YORK
DEPT,LOC,DALLAS
DEPT,LOC,CHICAGO
DEPT,LOC,BOSTON
DEPT,LOC,BOSTON
SALGRADE,GRADE,1
SALGRADE,GRADE,2
SALGRADE,GRADE,3
SALGRADE,GRADE,4
SALGRADE,GRADE,5
SALGRADE,GRADE,5
SALGRADE,LOSAL,700
SALGRADE,LOSAL,1201
SALGRADE,LOSAL,1401
SALGRADE,LOSAL,2001
SALGRADE,LOSAL,3001
SALGRADE,LOSAL,3001
SALGRADE,HISAL,1200
SALGRADE,HISAL,1400
SALGRADE,HISAL,2000
SALGRADE,HISAL,3000
SALGRADE,HISAL,9999
SALGRADE,HISAL,9999

PL/SQL procedure successfully completed.

SQL>
SQL>