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?

  • 2
    ID columns [in a sensibly designed schema] are normally keyed and cannot be NULL. Have you checked this first? – Philᵀᴹ Oct 25 '12 at 11:11
  • @Phil: that is what he tries to find out... – ppeterka Oct 25 '12 at 11:23
  • @ppeterka No. Read my comment again. The DDL for the column would explicitely state NOT NULL, which is distinctly different from checking each individual table column value recursively to check for NULL values. Your answer is incorrect. – Philᵀᴹ Oct 25 '12 at 11:31
  • 1
    If the DDL states NOT NULL, then there is no way you can put a NULL value in that column. Maybe your question is incorrect! – ppeterka Oct 25 '12 at 11:34
  • @Phil I know that if the ddl states not null then its impossible for that ID to have null value but my client says that he have seen id with null value and system have crashed because of that. Now i just want to make a script for him so he can see that no tables with id have null value. – Samir Oct 25 '12 at 12:11

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:

  l_cnt pls_integer;
  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
    end if;
  end loop;
  • It seems that i dont have so much privilegies to query against dba_tab_cols. is there another way? – Samir Oct 25 '12 at 12:07
  • 1
    You can replace dba_ with all_ (everything you have privilege to see) or user_ (just the objects your user owns). If you have multiple schemas with objects in the T_ECOS tablespace you'll need to ensure you have privileges to access them all. – Chris Saxon Oct 25 '12 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.