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

What are you trying to accomplish? If you want dynamic sql you either have to use EXECUTE IMMEDIATE statement or DBMS_SQL package.. I would go read up on those. — OldProgrammer
– OldProgrammer, Commented May 22, 2020 at 17:21
I'm trying to perform some statistical analysis on each column in each table in my database with PL/SQL — Jelly
– Jelly, Commented May 22, 2020 at 17:24

Connor McDonald · Accepted Answer · 2020-05-25 08:42:43Z

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>

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related