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);