How can I use a table name reflexively?

From: Mike58 <n00spam_at_comcast.net>
Date: 10 Aug 2004 09:06:04 -0700
Message-ID: <b19f7766.0408100806.231e0549@posting.google.com>

I would like to check each table in a particular schema that has column LAST_UPD_DT and only report those tables that have values for LAST_UPD_DT > TO_DATE(<some date string>, 'DD-MMM-YYYY').

My guess is that I need to load the TABLE_NAMEs into a variable and than test the column of each table in a loop. Something like:

CREATE OR REPLACE PROCEDURE find_tables_with_last_upd_dt

     next_tab           VARCHAR2(100);
     CURSOR tab_cursor IS
          SELECT TABLE_NAME
          FROM ALL_CONS_COLUMNS
          WHERE OWNER = 'schema_name' AND COLUMN_NAME = 'LAST_UPD_DT'
          ORDER BY TABLE_NAME;

BEGIN
  OPEN tab_cursor;
  LOOP

      FETCH tab_cursor INTO
           next_tab;
           IF (tab_cursor%FOUND)
           THEN
                IF (schema_name.next_tab.LAST_UPD_DT > 
                   TO_DATE('<some date>', 'DD-MMM-YYYY')
                THEN
                    DBMS_OUTPUT.PUT_LINE(next_tab);
                END IF;
           END IF;

  EXIT WHEN tab_cursor%NOTFOUND;
  END LOOP;
  CLOSE tab_cursor;
END find_tables_with_last_upd_dt;
/                  

Except that I don't think that I can use next_tab reflexively, can I?

How would you do this? I suppose I could start punching SQL to do the check, a sort of two step approach, but I was hoping that Oracle had a better facility for doing this kind of thing.

--Thank you,
--Mike
Received on Tue Aug 10 2004 - 11:06:04 CDT