Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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

1 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;
/
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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