Oracle - iterate over tables and check for values in attribute

Question

For all tables in X, while X is

select table_name from all_tab_cols
where column_name = 'MY_COLUMN'
and owner='ADMIN'

I need to check, if the column MY_COLUMN has other values than 'Y' or 'N' and if it does, print out the table name.

Pseudo code:

for table in X:
    if MY_COLUMN !='Y' or MY_COLUMN !='N':
        print table

How to implement that in PL/SQL, with cursors I guess?

Answer 1

Following should work:

DECLARE
  counter NUMBER;
  cursor c1 is 
    select table_name from all_tab_cols
    where column_name = 'MY_COLUMN'
    and owner='ADMIN';

BEGIN
  FOR rec IN c1 LOOP
    DBMS_OUTPUT.PUT_LINE(rec.table_name);
    EXECUTE IMMEDIATE 'select count(*) into :counter from '|| rec.table_name ||' where MY_COLUMN!= ''Y'' and MY_COLUMN!= ''N'' ';
    if counter > 0 then
      DBMS_OUTPUT.PUT_LINE(rec.table_name);
    end if;
  END LOOP;
END;

Basically we open a cursor with all tables containing that column, do a count for rows that have different values than Y or N, and if that count > 0, print the table.