Need to dynamically generate a SQL SELECT which excludes NULL columns

I have a table with 100+ columns, for which I'm trying to retrieve only 1 specific record. For this single record, I do not know which of the columns are NULL, and which are populated.

I would like to create a dynamically-generated SELECT--limiting the columns to only those that are populated. If, for example, only columns COL1, COL8, and COL93 are populated for this one record in the MYTEST table, the generated SELECT statement would be:

    select COL1, COL8, COL93
    from MYTEST
    where COL1='current_value';

as opposed to:

    select COL1, COL2, COL3, COL4, COL5, COL6 . . .     from MYTEST
    where COL1='current_value';

In PL/SQL, I've been wrangling to do this--obtaining the list of columns from USER_TAB_COLUMNS for the table, using a temporary table to store both the column's value and the column's name for further analysis, etc. However, this is seemingly cumbersome. Isn't there an easier way to efficiently identify which columns are populated or not for a specific row in a table?

Thanks in advance.
-Tom Received on Tue Jul 08 2003 - 21:27:34 CEST