How to get only not Null columns from Table

DECLARE
l_table_name VARCHAR2(30) := ‘HZ_LOCATIONS’;
l_select VARCHAR2(32767);
l_test_select VARCHAR2(32767);
l_value PLS_INTEGER;

CURSOR c IS
SELECT column_name
,nullable
FROM ALL_TAB_COLS
WHERE table_name = l_table_name
AND USER_GENERATED = ‘YES’
ORDER BY COLUMN_ID;

BEGIN
l_select := ‘select ‘;
FOR r IN c
LOOP
— Can’t be null
IF r.nullable = ‘N’
THEN
l_select := l_select || r.column_name || ‘,’;
ELSE
BEGIN
l_test_select := ‘select 1 from dual where exists (select 1 from ‘ || l_table_name || ‘ where ‘ ||
r.column_name || ‘ is not null)’;
dbms_output.put_line(l_test_select);
EXECUTE IMMEDIATE l_test_select
INTO l_value;
— If it has value then add to the select statement —
l_select := l_select || r.column_name || ‘,’;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
END IF;
END LOOP;

— create a select statement
l_select := substr(l_select, 1, length(l_select) – 1) || ‘ from ‘ || l_table_name;
dbms_output.put_line(‘SQL -‘||l_select);
END;

This entry was posted in Oracle.

Leave a Reply

Your email address will not be published. Required fields are marked *