Oracle ETL测试：如何验证非空列中的空值？求示例查询

Hey there! As someone new to Oracle diving into ETL testing, validating that non-null columns don’t contain null values is a critical check to ensure data integrity. Let’s break down how to do this with clear, reusable queries:

1. First: Identify Non-Null Columns in Your Tables #

Before writing checks, it’s smart to confirm which columns are supposed to be non-null. Run this query to get the list for T1 (swap 'T1' for 'T2' or other tables as needed):

SELECT column_name
FROM user_tab_columns
WHERE table_name = 'T1'  -- *Note: Oracle stores table/column names in uppercase by default*
  AND nullable = 'N';

This will return all columns in T1 that have a non-null constraint.

2. Check for Nulls in a Single Table #

Option A: Find Rows with Null Values

If you want to see the actual rows that violate the non-null constraint (great for debugging), use this:

-- Replace col1, col2 with your non-null columns from the previous step
SELECT *
FROM T1
WHERE col1 IS NULL 
   OR col2 IS NULL;

If no rows are returned, your non-null columns are clean!

Option B: Count Nulls Per Column

For a quick summary (perfect for test reports), use aggregate counts to see how many nulls exist in each non-null column:

SELECT
  COUNT(CASE WHEN col1 IS NULL THEN 1 END) AS col1_null_count,
  COUNT(CASE WHEN col2 IS NULL THEN 1 END) AS col2_null_count
FROM T1;

A value of 0 means no nulls for that column—exactly what you want in ETL testing.

3. Batch Check Multiple Tables (T1, T2, etc.) #

If you need to validate multiple tables at once, this PL/SQL block will automate the check and output results for every non-null column in your target tables:

SET SERVEROUTPUT ON;
DECLARE
  v_table_name VARCHAR2(100);
  v_col_name VARCHAR2(100);
  v_null_count NUMBER;
BEGIN
  -- Loop through all non-null columns in T1 and T2
  FOR col_rec IN (
    SELECT table_name, column_name
    FROM user_tab_columns
    WHERE table_name IN ('T1', 'T2')
      AND nullable = 'N'
  ) LOOP
    -- Build and execute the count query dynamically
    EXECUTE IMMEDIATE 
      'SELECT COUNT(*) FROM ' || col_rec.table_name || ' WHERE ' || col_rec.column_name || ' IS NULL'
      INTO v_null_count;
    
    -- Print the results
    DBMS_OUTPUT.PUT_LINE('Table: ' || col_rec.table_name || ', Column: ' || col_rec.column_name);
    DBMS_OUTPUT.PUT_LINE('Number of null values: ' || v_null_count);
    DBMS_OUTPUT.PUT_LINE('------------------------------');
  END LOOP;
END;
/

Run this in SQL*Plus or your Oracle client, and you’ll get a clear breakdown of null counts across all your target non-null columns.

Quick Tips #

• If your tables were created with lowercase names, wrap them in double quotes (e.g., "t1" instead of T1) in your queries.
• For tables owned by another user, use all_tab_columns instead of user_tab_columns and add AND owner = 'YOUR_SCHEMA_NAME' to the where clause.

内容的提问来源于stack exchange，提问作者lifeofpy