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.

Presumably your `REGEXP_LIKE` against the column name isn't matching anything, otherwise you'd be getting errors - you need dynamic SQL to access a table column value when you don't know the names at compile time, and your subquery would probably find multiple rows anyway. [Are you looking for this kind of thing?](https://stackoverflow.com/q/208493/266304) — Alex Poole, Jul 02 '18 at 11:10
@AlexPoole This is similar to what i needed. I was able to glean a couple useful tips from here too so thanks for the pointer. — SSingh, Jul 02 '18 at 15:43

Flavian-Calinic Stuparu · Accepted Answer · 2018-07-02T11:57:53.870

3

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;

edited Jul 02 '18 at 11:57

answered Jul 02 '18 at 11:20

Flavian-Calinic Stuparu

169
9

1

@KaushikNayak - you could do that for the literals, but not to get the actual column value, or in the `from`/`where` clauses. Those all have to be fixed identifiers when the query is parsed, they can't be bound. Still, I'd use concatenation instead of replace, personally, although replace would let you define the placeholder query string once outside the loop if you had two variables, I suppose; and I would avoid using `:` for the placeholders as they imply bind variables, which is just a bit confusing. – Alex Poole Jul 02 '18 at 11:42
@Alex Poole, yes, that is why it does not work here, thank you both. – Flavian-Calinic Stuparu Jul 02 '18 at 11:46
1

@AlexPoole : I agree. I didn't see it clearly that it is being used for column /table names. – Kaushik Nayak Jul 02 '18 at 11:46
2

@Flavian-CalinicStuparu : That's completely true +1. Although its a good answer, I would like to say that we could further improve this by writing an implicit cursor loop than `open..fetch..loop` and avoiding variables as Alex said. – Kaushik Nayak Jul 02 '18 at 11:50
@KaushikNayak you are right, is neater, and it should even be faster this way, as in later versions of Oracle, the implicit cursor loop acts like a BULK operation. – Flavian-Calinic Stuparu Jul 02 '18 at 12:01
1

@Flavian-CalinicStuparu thank you for this, it does what i needed it to do. Took about an hour so not as long as I anticipated. – SSingh Jul 02 '18 at 15:42

Data not inserting to destination table

1 Answers1