Totally Dynamic in 10g

Sometimes you need to pull record history data from Apps tables for audit purposes. Usually, say for invoices or bank account setups and transactions. You can write simple SQLs such as

SELECT * 
   FROM ap_bank_branches APBB
  WHERE ( APBB.created_by = :user_id AND TRUNC(APBB.creation_date) 
BETWEEN '01-DEC-2009' 
    AND '31-DEC-2009')
     OR  ( APBB.last_updated_by = :user_id AND TRUNC(APBB.last_update_date) 
BETWEEN '01-DEC-2009' 
    AND '31-DEC-2009')
;

and get it done or you can write a totally dynamic SQL generator such as one below.
This one is truly dynamic. FROM is set at runtime, COLUMN list is set at runtime, WHERE clause is also is set runtime.

lv_table_name := 'AP_BANK_BRANCHES';
      ln_user_id    := 1234;
      ld_start_date := SYSDATE-30;
      ld_end_date   := SYSDATE;

      IF lv_table_name IS NOT NULL
      THEN
        fnd_file.new_line(fnd_file.log,1);
        fnd_file.put_line(fnd_file.log,RPAD('-',1000,'-'));
        fnd_file.put_line(fnd_file.log,'Processing Table Name ' || lv_table_name);
        fnd_file.put_line(fnd_file.output,RPAD('-',1000,'-'));
        fnd_file.put_line(fnd_file.output,'Table Name ' || lv_table_name);
        fnd_file.put_line(fnd_file.output,RPAD('-',1000,'-'));

        lv_qry_state := NULL;
        lv_col_list  := NULL;

        -- 
        -- Get only printable columns
        --
        FOR col_rec IN (SELECT LOWER(column_name) column_name
                          FROM dba_tab_columns
                         WHERE data_type IN ('NUMBER','DATE','VARCHAR','VARCHAR2','CHAR')
                           AND table_name = lv_table_name
                         ORDER BY column_id )
        LOOP
         IF lv_col_list IS NULL THEN
           lv_col_list := col_rec.column_name;
         ELSE
           lv_col_list := lv_col_list ||' ,'||col_rec.column_name;
         END IF;
        END LOOP;

        IF lv_col_list IS NULL THEN
          fnd_file.put_line(fnd_file.log,'Column list NULL for ' ||lv_table_name ||', verify if it is a table');
        ELSE
          lv_table_name := LOWER(TRIM(lv_table_name));
          lv_qry_state :='
 SELECT ' || lv_col_list   ||'
   FROM ' || lv_table_name ||'
  WHERE ( ( created_by      = :u1 AND TRUNC(creation_date)    BETWEEN :d1 AND :d2 )
     OR   ( last_updated_by = :u2 AND TRUNC(last_update_date) BETWEEN :d3 AND :d4 )
        )' ;

          fnd_file.put_line(fnd_file.log,'START ' || TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
          fnd_file.put_line(fnd_file.log,lv_qry_state );

          l_theCursor     := dbms_sql.open_cursor;
          dbms_sql.parse(  l_theCursor,  lv_qry_state, dbms_sql.native );

          dbms_sql.bind_variable(l_theCursor, ':u1', ln_user_id);
          dbms_sql.bind_variable(l_theCursor, ':u2', ln_user_id);

          dbms_sql.bind_variable(l_theCursor, ':d1', ld_start_date);
          dbms_sql.bind_variable(l_theCursor, ':d2', ld_end_date);

          dbms_sql.bind_variable(l_theCursor, ':d3', ld_start_date);
          dbms_sql.bind_variable(l_theCursor, ':d4', ld_end_date);

          dbms_sql.describe_columns2 ( l_theCursor, l_numcols, l_columns );

          FOR i IN 1 .. l_numcols LOOP
            BEGIN
              IF l_columns( l_numcols).col_type IN (1,2,9,12,96)
              THEN
                dbms_sql.define_column( l_theCursor, i,
                                        l_columnValue, 2000 );
                l_colCnt := i;
                fnd_file.put(fnd_file.output, '"'||l_columns( l_colCnt).col_name||'",');
              END IF;
            EXCEPTION
              WHEN OTHERS THEN
                fnd_file.put_line(fnd_file.log,'SQLERRM ' || SQLERRM );
                IF ( SQLCODE = -1007 ) THEN
                  EXIT;
                ELSE
                  RAISE;
                END IF;
            END;
          END LOOP;

          fnd_file.put_line(fnd_file.output, ' ');
          dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                                    2000 );

          l_status := dbms_sql.execute(l_theCursor);
          l_cnt    := 0;
          LOOP
            EXIT WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
            l_cnt := l_cnt+1;
            FOR i in 1 .. l_colCnt LOOP
               /*
                TYPECODE_VARCHAR         PLS_INTEGER :=   1;
                TYPECODE_NUMBER          PLS_INTEGER :=   2;
                TYPECODE_VARCHAR2        PLS_INTEGER :=   9;
                TYPECODE_DATE            PLS_INTEGER :=  12;
                TYPECODE_CHAR            PLS_INTEGER :=  96;
                This condition is redundant here as we have taken care
                of printable columns in the column list generation, but
                good to know what type is what number!
               */
               IF l_columns( l_colCnt).col_type IN (1,2,9,12,96)
               THEN
                 dbms_sql.column_value( l_theCursor, i,
                                        l_columnValue );
                 fnd_file.put( fnd_file.output, '"'||NVL(TRIM(SUBSTR(REPLACE(l_columnValue,'"',''),1,2000)),' ')||'",' );
               ELSE
                 fnd_file.put( fnd_file.output, '"'||' '||'",' );
               END IF;
            END LOOP;
            fnd_file.put_line(fnd_file.output,' ');
          END LOOP;

          dbms_sql.close_cursor(l_theCursor);
          fnd_file.new_line(fnd_file.output,2);
          fnd_file.put_line(fnd_file.log,'Record Count ' || l_cnt);
          fnd_file.put_line(fnd_file.log,'END ' || TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
        END IF; -- col list not null
      END IF; -- table not null

You could make it even more dynamic. Create a lookup that holds the table names and loop through the table names.

I have tried this in 10g and it works. Not sure about 9i though.

Cheers.