How to select columns from a table which have non null values?

Have a look as statistics information, it may be useful for you:

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';

  NUM_ROWS
----------
        14

SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
  2  where owner='SCOTT' and table_name='EMP' order by column_id;

COLUMN_NAME                    N NUM_DISTINCT  NUM_NULLS
------------------------------ - ------------ ----------
EMPNO                          N           14          0
ENAME                          Y           14          0
JOB                            Y            5          0
MGR                            Y            6          1
HIREDATE                       Y           13          0
SAL                            Y           12          0
COMM                           Y            4         10
DEPTNO                         Y            3          0

8 rows selected.

For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.

I don't think this can be done in a single query. You may need some plsql to first test what columns contain data and put together a statement based on that information. Of course, if the data in your table changes you have to recreate the statement.

declare

   l_table          varchar2(30) := 'YOUR_TABLE';
   l_statement      varchar2(32767);
   l_test_statement varchar2(32767);

   l_contains_value pls_integer;

   -- select column_names from your table
   cursor c is
      select column_name
            ,nullable
        from user_tab_columns
       where table_name = l_table;

begin
   l_statement := 'select ';
   for r in c
   loop
      -- If column is not nullable it will always contain a value
      if r.nullable = 'N'
      then
         -- add column to select list.
         l_statement := l_statement || r.column_name || ',';
      else
         -- check if there is a row that has a value for this column
         begin
            l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' ||
                                r.column_name || ' is not null)';
            dbms_output.put_line(l_test_statement);
            execute immediate l_test_statement
               into l_contains_value;


            -- Yes, add column to select list
            l_statement := l_statement || r.column_name || ',';
         exception
            when no_data_found then
               null;
         end;

      end if;
   end loop;

   -- create a select statement
   l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table;

end;

This block determines all columns in the table, loops through them in dynamic SQL and checks if they are null, then constructs a DBMS output query of the non-null query.

All you have to do is run the returned query.

I've included the exclusion of PKs and BLOB columns. Obviously, this is quite slow as going through columns one by one, and it's not going to be great for very hot tables, as data may change too quickly, but this works for me as I control traffic in dev env.

DECLARE
  l_table_name      VARCHAR2(255) := 'XXXX';
  l_counter         NUMBER;
  l_sql             CLOB;

BEGIN
  FOR r_col IN (SELECT * 
                FROM user_tab_columns tab_col
                WHERE table_name = l_table_name
                AND data_type NOT IN ('BLOB')
                AND column_name NOT IN (SELECT column_name 
                                        FROM user_cons_columns con_col
                                        JOIN user_constraints cons ON con_col.constraint_name = cons.constraint_name AND con_col.table_name = cons.table_name
                                        WHERE con_col.table_name = tab_col.table_name
                                        AND constraint_type = 'P')
                ORDER BY column_id) 
  LOOP

    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_table_name||' WHERE '||r_col.column_name||' IS NOT NULL'
    INTO l_counter;
    
    IF l_counter > 0 THEN
      IF  l_sql IS NULL THEN 
        l_sql := r_col.column_name;
      ELSE   
        l_sql :=  l_sql||','||r_col.column_name;
      END IF;
    END IF;
   
  END LOOP;  

  l_sql :=  'SELECT '||l_sql||CHR(10)
         ||'FROM '||l_table_name;
          
  ----------
  DBMS_OUTPUT.put_line(l_sql);  
END;

What you're asking to do is establish a dependency on each row in the whole result. This is in fact not ever what you want. Just think of the ramifications if in one row every column had a value of '0' -- suddenly the schema of your result set grows to include all of those previously "empty" columns. You're effectively growing the badness of '*' exponentially, now your result set is not dependent on just the table's meta-data -- but your whole result set is dependent on the plain data.

What you want to do is just select the fields that have what you want, and not deviate from this simple plan.