Programmers Blog

This code will look through all tables in the current schema and find a column in a table that has a row with the specified value “XYZ”. The column name must include the strings “ID” and “OFFER”.

DECLARE
  CURSOR all_columns
  IS
     SELECT table_name,
      column_name
       FROM user_tab_cols
      WHERE upper(column_name) LIKE '%ID%'
    AND upper(column_name) LIKE '%OFFER%';
  
  wartosc  VARCHAR(200);
  sql_stmt VARCHAR(200);
BEGIN
  FOR t IN all_columns
  LOOP
    sql_stmt := 'begin select ' || t.column_name || ' into :a from ' || t.TABLE_NAME || ' where ' || t.column_name || ' = ''XYZ''; end;';
    --DBMS_OUTPUT.PUT_LINE(sql_stmt);
    BEGIN
      EXECUTE IMMEDIATE sql_stmt USING OUT wartosc;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      GOTO next_one;
      -- DBMS_OUTPUT.PUT_LINE('Table: ' || t.TABLE_NAME || ' Column: ' || t.column_name || ' NO DATA');
    END;
    DBMS_OUTPUT.PUT_LINE('FOUND-> Table: ' || t.TABLE_NAME || ' Column: ' || t.column_name);
    <<next_one>> NULL;
  END LOOP;
END;