Find ID columns with null value in oracle database

Question

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database

Any suggestion?

Answer 1

You can do something like this to dynamically generate a query against the tables with a nullable ID column to see whether they actually contain nulls or not:

declare
  l_cnt pls_integer;
begin
  for tabs in (SELECT c.owner, c.table_name FROM dba_segments s, dba_tab_cols c
               where  s.segment_name = c.table_name
               and    c.column_name = 'ID'
               and    c.nullable = 'Y'
               and    s.tablespace_name = 'T_ECOS') loop
    execute immediate 'select count(*) from ' || tabs.owner || '.' || tabs.table_name || 
      ' where id is null and rownum = 1' -- to short-circuit the count as soon as a null entry is found
      into l_cnt;

    if l_cnt > 0 then
      dbms_output.put_line(tabs.table_name);
    end if;
  end loop;
end;
/