Generic null check procedure

I have a requirement to check all columns in a number of tables for null values. The environment is 7.3.3 on vms, using sql*plus and pl/sql. I would like the process to be generic, so that I can run it against any table without modification.

The output requirement is to show the primary key values with the column name that contains a null (not necessarily in this format). For example:

CUSTOMER_NO ADDRESS1

1234        NULL
4867        NULL

CUSTOMER_NO PHONE

1234        NULL
8745        NULL

Does anyone know of an existing script to do this type of check ? I'm hoping that someone has done something like this before.

If there's nothing existing, I have written a procedure with some dynamic sql (dbms_sql) to loop though the columns in a table and do a check for nulls.

The part giving me grief is that the primary key may be made up of more than one column. I'm not sure how to handle the variable number of columns that may be returned. The procedure accepts the table_name to scan and a list of columns to return, in addition to the null-found column (exec x('table1','col1, col2')). I realize that the primary key columns can be generated dynamically as well, but I left that for now. I'm using dbms_output.put_line for output.

I can get it down to finding the column names required to be reported and populating a pl/sql table with all of the necessary select statements, such as:

select a,b,c from table1;
select a,b from table2;
...

What is the easiest way to process these selects to get my output ? I could spool a file in sql*plus and then run it, but this check is one of a number and I'm already spooling output to a log file (I couldn't find a way to spool to a log file, spool to a temp file, spool back to the log file, then run the temp file without overwriting the log file with the 2nd spool. I also couldn't find a way to capture the spool filename for the 2nd spool.).

Thanks
--
Corey Gill
coreygill_at_xwavesolutions.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 12 1999 - 11:46:54 CDT