Data not inserting to destination table

Question

I have the following block of PL-SQL code in Oracle:

DECLARE  TAB VARCHAR(100);
         COL VARCHAR(100);

CURSOR C_COLS IS
          select DISTINCT table_name, column_name
          from all_tab_columns 
          where OWNER = 'MyDB' AND DATA_TYPE LIKE '%VARCHAR%';
BEGIN
    OPEN C_COLS;
    LOOP
      FETCH C_COLS INTO TAB, COL;
      EXIT WHEN C_COLS%notfound; 
        INSERT INTO TargetTable (TABLE_NAME, COLUMN_NAME, COLUMN_VALUE)
        SELECT  DISTINCT   TAB, 
                           COL,
                           (SELECT COL FROM TAB)
FROM TAB
WHERE REGEXP_LIKE(COL, '([ABCDEFGHIJKLMNOPQRSTUVWXYZ])\d\d\d\d\d\d([ABCDEFGHIJKLMNOPQRSTUVWXYZ])', 'ix');
     END LOOP;
     CLOSE C_COLS;
END;

The idea is to determine which tables in my rather large database contain a certain pattern of data and to find them.

So I want to return three columns: TableName, ColumnName, Value of ColumnName.

The above runs but returns no data and I can't understand why. The query in the cursor returns results, and if I hard code the table values into a simple select statement containing my Regex, I get results. I just want one result set that contains the thousands of results I expect.

Could it be the (SELECT COL FROM TAB) I'm using to dynamically find the column_value? I wasn't sure if I could express it this way.

Answer 1

If you want to select columns dynamically you may wish to try dynamic SQL.

DECLARE  
  w_sql VARCHAR2(32767);
BEGIN
  DBMS_OUTPUT.enable(32767);
  FOR s_cols IN (
    select DISTINCT 
           table_name
         , column_name
      from all_tab_columns 
     where owner = 'MyDB' 
       AND data_type LIKE '%VARCHAR%'
  )
  LOOP
    w_sql := q'!
      INSERT 
        INTO TargetTable (TABLE_NAME, COLUMN_NAME, COLUMN_VALUE)
      SELECT DISTINCT 
             ':TAB'
           , ':COL'
           , :COL
        FROM :TAB
       WHERE REGEXP_LIKE(:COL, '([ABCDEFGHIJKLMNOPQRSTUVWXYZ])\d\d\d\d\d\d([ABCDEFGHIJKLMNOPQRSTUVWXYZ])', 'ix')
    !';
    w_sql := REPLACE(w_sql, ':TAB', s_cols.table_name);
    w_sql := REPLACE(w_sql, ':COL', s_cols.column_name);
    EXECUTE IMMEDIATE w_sql;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error for SQL :'|| w_sql ||'; error is :'|| SQLERRM);
END;