Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Identify Null and Blank values

    Hi,

    I want to identify blank and null values for a table. I have create two procedures for this.
    These procedures are runing fast for small table but taking lot of time for big table.
    Can there be better method than this?
    Proceure's
    PHP Code:
    Create or replace procedure P_get_blank_values(object_name in varchar2)as
    sqltext varchar2(200);
    cnt integer;
    begin
     
    For i in (select column_name from all_tab_columns where owner='TEST' and table_name=object_name and data_type in ('VARCHAR2','CHAR')) loop
     sqltext
    :='select count(1) from TEST.'||object_name||' where '||i.column_name||'='||''' ''';
     
    execute immediate sqltext into cnt;
     
    dbms_output.put_line('Blank values : '||i.column_name||' count: '||cnt);
    --
    dbms_output.put_line(sqltext);
    end loop;
    end
    PHP Code:
    Create or replace procedure P_get_null_values(object_name in varchar2)as
    sqltext varchar2(200);
    cnt integer;
    begin
    For i in (select column_name from all_tab_columns where owner='TEST' and table_name=object_nameloop
    sqltext
    :='select count(1) from TEST.'||object_name||' where '||i.column_name||' is null ';
    execute immediate sqltext into cnt;
    dbms_output.put_line('Null values : '||i.column_name||' count: '||cnt);
    --
    dbms_output.put_line(sqltext);
    end loop;
    end
    I dont want to used NUM_NULLS column from table all_tab_columns to identify the null rows as it depend on analyzing table.

    thanks,
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, you could combine the 2 procedures into 1 to halve the processing time:
    Code:
    Create or replace procedure P_get_blank_values(object_name in varchar2)as
      sqltext varchar2(200);
      cnt_blank integer;
      cnt_null integer;
    begin
      For i in (select column_name from all_tab_columns where owner='TEST' and table_name=object_name and data_type in ('VARCHAR2','CHAR')) loop
        sqltext:='select sum(decode(' || i.column_name || ','' '',1,0)) cnt_blank'
              || ',      sum(decode(' || i.column_name || ',NULL,1,0)) cnt_null'
              || ' from TEST.'||object_name
              || ' where '||i.column_name='' '' or i.column_name is null';
        execute immediate sqltext into cnt_blank, cnt_null;
        dbms_output.put_line('Blank values : '||i.column_name||' count: '||cnt_blank);
        dbms_output.put_line('Null  values : '||i.column_name||' count: '||cnt_null);
        --dbms_output.put_line(sqltext);
      end loop;
    end;
    But you will still be performing N full table scans on the table, where N is the number of columns in the table.

    You say you don't want to use NUM_NULLS as this requires analyzing the tables. But you should be analyzing the tables anyway, should you not?

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi ,

    Thanks for the reply. I don't want to use all_tab_columns because
    we are analyzing all object by cron job. Some of the developers want to
    test the null values for the tables which are created few hours before and which are not analyzed.
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could just include an "analyze table" (or DBMS_STATS call) in your procedure:
    Code:
    Create or replace procedure P_get_null_values(object_name in varchar2)as
      cnt_null integer;
    begin
      dbms_stats.gather_table_stats('TEST',object_name);
      For i in (select column_name, num_nulls from all_tab_columns where owner='TEST' and table_name=object_name and data_type in ('VARCHAR2','CHAR')) loop
        dbms_output.put_line('Null  values : '||i.column_name||' count: '||i.num_nulls);
      end loop;
    end;
    However, that doesn't help with your search for single blanks. Do you really need that also? What about columns set to a string of 2 blanks, or 3 blanks?

    Using DBMS_SQL you could write dynamic SQL to count all the NULLs and all the blanks in one full scan of the table. You would have to use ALL_TAB_COLUMNS to generate query text something like:
    Code:
    select sum(decode(column1,' ',1,0)) column1_blanks
    ,      sum(decode(column1,null,1,0)) column1_nulls
    ,      sum(decode(column2,' ',1,0)) column2_blanks
    ,      sum(decode(column2,null,1,0)) column2_nulls
    ,      sum(decode(column3,' ',1,0)) column3_blanks
    ,      sum(decode(column3,null,1,0)) column3_nulls
    ...
    from table;

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thank you very much for your help!!
    if i correctly understand ur last quote, are you saying that the procedure will
    only identify single blanks and it will not search string of 2 and more blanks.?

    We have only single blank and not more than it. I tested the procedure and it seems its working fine.

    I can not write analyze comand inside the procedure as we have big tables and will take time to analyze.

    Generating sql query through dbms_sql is a good idea. But displaying output
    on sqlprompt will not be not readable?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The DBMS_SQL code would be more complex, but more efficient. You could get the readable output you require by including the column names in the SQL, e.g.
    Code:
    select 'COLUMN1' column1_name
    ,      sum(decode(column1,' ',1,0)) column1_blanks
    ,      sum(decode(column1,null,1,0)) column1_nulls
    ,      'COLUMN2' column2_name
    ,      sum(decode(column2,' ',1,0)) column2_blanks
    ,      sum(decode(column2,null,1,0)) column2_nulls
    ,      'COLUMN3' column3_name
    ,      sum(decode(column3,' ',1,0)) column3_blanks
    ,      sum(decode(column3,null,1,0)) column3_nulls
    ...
    from table;
    Then you could loop through the values returned and display the information something like this:
    Code:
    l_col := 1;
    while l_col < l_numcols loop
       dbms_sql.column_value( l_cursor, l_col, l_column_name );
       dbms_sql.column_value( l_cursor, l_col+1, l_num_blanks );
       dbms_sql.column_value( l_cursor, l_col+2, l_num_nulls );
       dbms_output.put_line('Column : '||l_column_name||' blanks: '||l_num_blanks||' nulls: '||l_num_nulls);   
       l_col := l_col + 3;
    end loop;
    You could play with the formatting a bit as you like...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Have you checked for more then one blank or do you think that there are only one blank record. Try the following

    select count(*)
    from my_table
    where field is not null
    and rtrim(field) is null;

    This will find a "blank" record, no matter haw many blanks there are in it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •