Re: How can I use a table name reflexively?

On 10 Aug 2004 09:06:04 -0700, n00spam_at_comcast.net (Mike58) wrote:

>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

>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

           declare dummy char(1);
            begin
              execute immediate 'select 'x' from dual where exists '||
              '(select 1 from '||next_tab||'where last_up_dt >
to_date(''<some_date>'',''dd-mm-yyyy'')' into dummy;
                    DBMS_OUTPUT.PUT_LINE(next_tab);
             exception
                  when no_data_found then 
                         null;
              end;


>           END IF;

Hth

--
Sybrand Bakker, Senior Oracle DBA