Go over all columns in tables in Oracle

Question

This is a noobie question, most likely syntax one. But I am kinda lost...

I need to go over all columns in all tables in Oracle to generate trigger script. That trigger should insert the row being updated to a log table which is nearly the same as the original table. I thought I would just go over all the columns and just concatenate strings. Fairly easy but I am struggling with the syntax...

Here's what I have so far:

DECLARE
   cursor tableNames is
      select table_name
      from user_tables
      where table_name not like '%_A';
    lSql varchar2(3000);
    type t_columnRow is ref cursor;

    v_columns t_columnRow;
begin

FOR tableName in tableNames
LOOP
    open v_columns for select COLUMN_NAME from user_tab_columns where table_name = tableName;

    for columnRow in v_columns LOOP
        DBMS_OUTPUT.PUT_LINE(tableName || '.' || columnRow.COLUMN_NAME);
        -- Here I would just concatenate the strings ....
    END LOOP;

END LOOP;    

End;

For that I am getting the following error:

Error at line 1
ORA-06550: line 14, column 84:
PLS-00382: expression is of wrong type
ORA-06550: line 16, column 22:
PLS-00221: 'V_COLUMNS' is not a procedure or is undefined
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored

Answer 1

You may be able to get away with something as simple as this:

DECLARE
   cursor tableNames is
      select table_name
      from user_tables
      where table_name not like '%_A';
    lSql varchar2(3000);
begin

FOR tableName in tableNames
LOOP   
    for columnRow in (select COLUMN_NAME from user_tab_columns where table_name = tableName) LOOP
        DBMS_OUTPUT.PUT_LINE(tableName || '.' || columnRow.COLUMN_NAME);
        -- Here I would just concatenate the strings ....
    END LOOP;

END LOOP;    

End;

Answer 2

Try this:

BEGIN
    FOR t IN (SELECT table_name FROM user_tables WHERE table_name not like '%_A')
    LOOP
        FOR c IN (SELECT column_name FROM user_tab_columns WHERE table_name = t.table_name)
        LOOP
            DBMS_OUTPUT.PUT_LINE(t.table_name||'.'||c.column_name);
            -- Here I would just concatenate the strings ....
        END LOOP;
    END LOOP;
END;