step3

-- 1. Declare the bind variable where the results will be stored.
-- The name of the cursor variable is ':p_result'
VAR p_result REFCURSOR;

DECLARE
    -- Define a custom type to hold the result row structure
    TYPE t_table_stats IS RECORD (
        table_name_col     VARCHAR2(40),
        rows_last_45_days  NUMBER,
        total_rows_approx  NUMBER,
        percentage_change  NUMBER
    );

    -- Define a collection type to hold all results
    TYPE t_table_stats_tab IS TABLE OF t_table_stats INDEX BY PLS_INTEGER;
    l_results t_table_stats_tab;

    v_idx PLS_INTEGER := 0;

BEGIN
    -- Loop through the tables that meet the criteria
    FOR r IN (
        SELECT table_name
        FROM   user_tab_columns
        WHERE  column_name = 'INTERNTIMESTAMP'
          AND  data_type LIKE 'TIMESTAMP%'
        ORDER BY table_name
    )
    LOOP
        DECLARE
            v_cnt   NUMBER := 0;
            v_total NUMBER := 0;
        BEGIN
            -- Execute dynamic SQL to count rows in the last 45 days
            EXECUTE IMMEDIATE
                'SELECT COUNT(*) FROM "' || r.table_name || 
                '" WHERE INTERNTIMESTAMP >= SYSDATE - 45'
                INTO v_cnt;

            -- Get total rows from statistics
            SELECT num_rows INTO v_total
            FROM   user_tables
            WHERE  table_name = r.table_name;

            v_idx := v_idx + 1;

            -- Store the results in the collection
            l_results(v_idx).table_name_col    := r.table_name;
            l_results(v_idx).rows_last_45_days := v_cnt;
            l_results(v_idx).total_rows_approx := NVL(v_total, 0);
            l_results(v_idx).percentage_change := ROUND(v_cnt / GREATEST(v_total, 1) * 100, 2);

        EXCEPTION
            WHEN OTHERS THEN
                -- If an error occurs, log the error row instead of the data
                v_idx := v_idx + 1;
                l_results(v_idx).table_name_col    := r.table_name || ' → ERROR: ' || SQLERRM;
                l_results(v_idx).rows_last_45_days := NULL;
                l_results(v_idx).total_rows_approx := NULL;
                l_results(v_idx).percentage_change := NULL;
        END;
    END LOOP;

    -- Open the REF CURSOR to the content of the collection.
    -- This uses the standard SQL function TABLE() to convert the PL/SQL collection
    -- into a selectable result set.
    OPEN :p_result FOR 
        SELECT
            t.table_name_col AS TABLE_NAME,
            t.rows_last_45_days AS ROWS_LAST_45_DAYS,
            t.total_rows_approx AS TOTAL_ROWS_APPROX,
            t.percentage_change AS PCT
        FROM TABLE(CAST(l_results AS t_table_stats_tab)) t;

END;
/
-- The "PRINT" command is a SQL*Plus/SQL Developer command,
-- but the use of the bind variable ':p_result' is usually enough
-- for DbVisualizer to detect the output and display it.
-- You may need to run this SELECT in a separate statement if DbVisualizer
-- doesn't automatically show the cursor result:
-- SELECT * FROM TABLE(:p_result);