report5

BEGIN
  FOR r IN (
    SELECT table_name
    FROM user_tab_columns
    WHERE UPPER(column_name) = 'INTERNTIMESTAMP'
      AND data_type LIKE 'TIMESTAMP%'
    ORDER BY table_name
  ) LOOP
    DECLARE
      v_recent NUMBER;
      v_total NUMBER;
      v_sql VARCHAR2(4000);
    BEGIN
      -- Count recent rows
      v_sql := 'SELECT COUNT(*) FROM "' || r.table_name || 
               '" WHERE INTERNTIMESTAMP >= SYSDATE - 45';
      EXECUTE IMMEDIATE v_sql INTO v_recent;

      -- Only show if there are recent rows
      IF v_recent > 0 THEN
        -- Count total rows
        v_sql := 'SELECT COUNT(*) FROM "' || r.table_name || '"';
        EXECUTE IMMEDIATE v_sql INTO v_total;

        -- Use DbVisualizer's own output
        DBMS_OUTPUT.PUT_LINE(
          RPAD(r.table_name, 30) || ' | ' ||
          LPAD(TO_CHAR(v_recent, '999,999,999'), 15) || ' | ' ||
          LPAD(TO_CHAR(v_total, '999,999,999'), 15) || ' | ' ||
          TO_CHAR(ROUND(v_recent/GREATEST(v_total,1)*100, 2), '999.99') || '%'
        );
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with ' || r.table_name || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/