0

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?

Improve this question
5
  • 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

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;
/
Improve this answer
2
  • 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.