DECLARE
sql_st VARCHAR2(1000);
no_of_records NUMBER;
BEGIN
dbms_output.put_line ('Table|No of Records Changed');
dbms_output.put_line ('------|----------------------------------');
FOR C1 in (SELECT table_name
FROM all_tables a
WHERE table_name not lIKE '%$%'
AND table_name not lIKE '%_A'
AND exists (SELECT 1
FROM all_tab_columns b
WHERE a.table_name = b.table_name
AND b.column_name = 'LAST_UPDATE_DATE'
)
order by table_name)
LOOP
no_of_records := 0;
sql_st := 'Select count(1) from '|| c1.table_name ||' where last_update_date > TRUNC(SYSDATE)';
BEGIN
EXECUTE IMMEDIATE sql_st INTO no_of_records;
dbms_output.put_line (c1.table_name||'|'||no_of_records);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (c1.table_name ||'|'|| SQLERRM);
END;
END Loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Exception : '||SQLERRM);
END;
sql_st VARCHAR2(1000);
no_of_records NUMBER;
BEGIN
dbms_output.put_line ('Table|No of Records Changed');
dbms_output.put_line ('------|----------------------------------');
FOR C1 in (SELECT table_name
FROM all_tables a
WHERE table_name not lIKE '%$%'
AND table_name not lIKE '%_A'
AND exists (SELECT 1
FROM all_tab_columns b
WHERE a.table_name = b.table_name
AND b.column_name = 'LAST_UPDATE_DATE'
)
order by table_name)
LOOP
no_of_records := 0;
sql_st := 'Select count(1) from '|| c1.table_name ||' where last_update_date > TRUNC(SYSDATE)';
BEGIN
EXECUTE IMMEDIATE sql_st INTO no_of_records;
dbms_output.put_line (c1.table_name||'|'||no_of_records);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (c1.table_name ||'|'|| SQLERRM);
END;
END Loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Exception : '||SQLERRM);
END;
No comments:
Post a Comment