How to loop through columns with PL/SQL

Question

I have searched through and found only this problem: Loop through columns SQL it's similar in some ways, but doesn't concern PL/SQL and Oracle Database, therefore I'm Asking new Question.

I have a table with ca. 2000 rows and 600 columns. There are some columns comprised only NULLs in each row. What I want to do is to write a PL/SQL Procedure to remove those columns from the table. So I have met a problem, I wanted to loop through columns in PL/SQL with help of all_tab_columns view. You can see my code below (my table name is PreparedDocumentFeaturesValues):

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   all_row_count NUMBER;
   null_row_count NUMBER;
BEGIN
   SELECT count(*) 
   INTO all_row_count 
   FROM PreparedDocumentFeaturesValues;

   FOR columnItem IN (SELECT column_name 
                      FROM all_tab_columns 
                      WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      SELECT count(*) 
      INTO null_row_count 
      FROM PreparedDocumentFeaturesValues 
      WHERE columnItem.column_name IS NULL;

      IF all_row_count=null_row_count THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

The problem is that statement:

SELECT count(*) 
INTO null_row_count 
FROM PreparedDocumentFeaturesValues 
WHERE columnItem.column_name IS NULL;

has character type as a column_name and null_row_count always equals 0.

I'm pretty sure, here is somebody who know how can I cope with this problem (by improving the code above, or maybe is there any other way to do such a thing?> Thank you in advance for your help.

score 3 · Answer 1 · edited Oct 19 '11 at 18:17

Since you don't know the column name at compile time, your query would need to use dynamic SQL as well. Something like

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   all_row_count NUMBER;
   null_row_count NUMBER;
BEGIN
   SELECT count(*) 
     INTO all_row_count 
     FROM PreparedDocumentFeaturesValues;
   FOR columnItem IN (SELECT column_name 
                        FROM all_tab_columns 
                       WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      l_query := 'SELECT COUNT(*) ' ||
                 '  FROM PreparedDocumentFeaturesValues ' ||
                 ' WHERE ' || columnItem.column_name || ' IS NULL';
      EXECUTE IMMEDIATE l_query
         INTO null_row_count;

      IF all_row_count=null_row_count THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

You could probably also simplify the logic a bit by just counting the non-NULL rows

PROCEDURE dropNullColumns AS
   l_query VARCHAR2(10000);
   not_null_row_count NUMBER;
BEGIN
   FOR columnItem IN (SELECT column_name 
                        FROM all_tab_columns 
                       WHERE TABLE_NAME = UPPER('PreparedDocumentFeaturesValues'))
   LOOP
      l_query := 'SELECT 1 from (SELECT COUNT(*) ' ||
                 '  FROM PreparedDocumentFeaturesValues ' ||
                 ' WHERE ' || columnItem.column_name || ' IS NOT NULL ' ||
                 '   ) WHERE rownum < 2';
      EXECUTE IMMEDIATE l_query
         INTO not_null_row_count;

      IF not_null_row_count=0 THEN 
         l_query := 'ALTER TABLE PreparedDocumentFeaturesValues DROP COLUMN ' || columnItem.column_name;
         EXECUTE IMMEDIATE l_query;
      END IF;
   END LOOP;
END;

This also has the benefit that if you happen to have any indexes on any columns, the queries in the loop could potentially use those. And the query can stop as soon as it finds a single not-NULL value rather than scanning the entire table.

score 3 · Answer 2 · answered Oct 19 '11 at 17:27

I believe you want

execute immediate 'SELECT count(*) FROM PreparedDocumentFeaturesValues WHERE '|| columnItem.column_name||' IS NULL' into null_row_count;

Here's a more complete answer that will be more performant than what you have above.

DVLP SQL>create table foo as select * from dba_objects where rownum < 10;

Table created.

DVLP SQL>update foo set status = null;

9 rows updated.

DVLP SQL>
DVLP SQL>declare
  2    tab_name constant varchar2(32) := 'foo';
  3    not_null number;
  4  begin
  5      for x in (select column_name from all_tab_columns where table_name = upper(tab_name)) loop
  6        dbms_output.put('Checking '||tab_name||'.'||x.column_name);
  7        begin
  8          execute immediate 'select 1 from (select 1 from '||tab_name||
  9            ' where '||x.column_name||' is not null) where rownum = 1' into not_null;
 10          dbms_output.put_line('.');
 11        exception when NO_DATA_FOUND then
 12          dbms_output.put_line('...all null.');
 13        end;
 14      end loop;
 15  end;
 16  /
Checking foo.OWNER.
Checking foo.OBJECT_NAME.
Checking foo.SUBOBJECT_NAME...all null.
Checking foo.OBJECT_ID.
Checking foo.DATA_OBJECT_ID.
Checking foo.OBJECT_TYPE.
Checking foo.CREATED.
Checking foo.LAST_DDL_TIME.
Checking foo.TIMESTAMP.
Checking foo.STATUS...all null.
Checking foo.TEMPORARY.
Checking foo.GENERATED.
Checking foo.SECONDARY.
Checking foo.NAMESPACE.
Checking foo.EDITION_NAME...all null.

How to loop through columns with PL/SQL

2 Answers2

Linked