Find junk values in all tables and columns:

  1. Loop through all tables in a schema (Oracle 18c)
  2. Loop through each number and text column
  3. If columns have values that are junk, then add a statistic/record to a log table:
    • 0 (number)
    • (text; single space)
    • (text; double space)
    • 0 (text)
    • - (text)
    • NULL (text; not a true null)
    • <NULL> (text; not a true null)
  4. Get the data custodian to review the issue and correct it if applicable.

--TRUNCATE TABLE incorrect_value_results;
--CREATE TABLE incorrect_value_results (id NUMBER, table_name VARCHAR2(30), column_name VARCHAR2(30), val_count NUMBER);

   l_count NUMBER;
   l_index NUMBER;
   l_index := 0;
   -- Loop through each table in the schema
   FOR i IN (SELECT table_name
               FROM user_tables) LOOP
      -- Loop through each relevant column for this table
      FOR j IN (SELECT column_name, data_type
                  FROM user_tab_cols
                 WHERE table_name = i.table_name
                   AND data_type IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2', 'NUMBER')) LOOP
         IF j.data_type IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2') THEN
            EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||i.table_name||' WHERE UPPER('||j.column_name||') IN('' '', ''  '', ''0'', ''-'', ''NULL'', ''<NULL>'' )' INTO l_count;
         ELSIF j.data_type = 'NUMBER' THEN
            EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||i.table_name||' WHERE '||j.column_name||' <= 0' INTO l_count ;
         END IF;
         -- If there are results then log them
         IF l_count > 0 THEN
            l_index := l_index + 1; 
            INSERT INTO incorrect_value_results (id, table_name, column_name, val_count)
            VALUES (l_index,
         END IF;
      END LOOP;


I'm relatively new to PL/SQL. How can the script be improved?

  • 1
    \$\begingroup\$ Relevant thread: chat.stackexchange.com/rooms/179/conversation/gis-junk-values \$\endgroup\$
    – jcolebrand
    Commented Nov 3, 2020 at 18:22
  • 1
    \$\begingroup\$ Given your requirements (see above thread) and your work here, it seems like it's good enough to get the job done. Unfortunately I'm not a whiz-bang on Oracle's PLSQL so I can't give much advice there, but what I can say is that without us knowing a few more details, predicting the serviceability of SQL is gonna require some magical forward knowing. Like what's indexed vs what isn't, that sort of thing. - Overall this looks straightforward and reasonable. - You might consider an auditing service instead of a query and do this offline? \$\endgroup\$
    – jcolebrand
    Commented Nov 3, 2020 at 18:24
  • 1
    \$\begingroup\$ By "auditing service" I just mean "a piece of software that you write that is not a query in the database that can be run on a remote machine that 1) asks for a slot of data and 2) runs analysis on that slot of data". Benefits being that you being in program land and not query land means logging, flat file output, better potential for formatting, etc. Downsides are network transit and two codebases. \$\endgroup\$
    – jcolebrand
    Commented Nov 3, 2020 at 18:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.