Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<1t1ih0dt4g7ocljn88qhi1ko1gjetdm6ej_at_4ax.com>...
> 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:
> >
[snip]
>
> >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;
> > EXIT WHEN tab_cursor%NOTFOUND;
> > END LOOP;
> > CLOSE tab_cursor;
> >END find_tables_with_last_upd_dt;
> >/
>
>
> Hth
Ah. Dynamic SQL. Thank you! Mike
Received on Tue Aug 10 2004 - 22:09:08 CDT
