Tired of trying to get a proper Real Application Testing (RAT) comparison report in Oracle? Frustrated because Oracle docs, Google, or even AI couldn’t give you a full working solution? You’re in the right place – here’s the missing piece you’ve been searching for!

Step 1 – Identify Your Replay IDs

First, check your available workload replays:

SELECT id, name, status 
FROM dba_workload_replays;

Step 2 – Create a Directory for the Report

Oracle needs a directory object to write the HTML output:

CREATE DIRECTORY replay_reports AS '/path/to/your/directory';
GRANT WRITE ON DIRECTORY replay_reports TO your_user;

Step 3 – PL/SQL Block to Generate the HTML Report

Here’s the full working code that handles large CLOBs by writing in 32K chunks:

DECLARE
    report_clob  CLOB;
    file_handle  UTL_FILE.FILE_TYPE;
    l_pos        INTEGER := 1;
    l_chunk_size INTEGER := 32767; -- max UTL_FILE write size
    l_buffer     VARCHAR2(32767);
BEGIN
    -- Generate RAT Comparison Report
    DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT(
        replay_id1 => 1,
        replay_id2 => 2,
        format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML,
        result     => report_clob
    );

    -- Open file in the directory object
    file_handle := UTL_FILE.FOPEN('REPLAY_REPORTS', 'compare_report.html', 'w', l_chunk_size);

    -- Write CLOB in chunks
    LOOP
        l_buffer := DBMS_LOB.SUBSTR(report_clob, l_chunk_size, l_pos);
        EXIT WHEN l_buffer IS NULL;
        UTL_FILE.PUT(file_handle, l_buffer);
        l_pos := l_pos + l_chunk_size;
    END LOOP;

    -- Close the file
    UTL_FILE.FCLOSE(file_handle);
END;
/

Step 4 – Open Your Report

Navigate to the directory on your database server and open compare_report.html in your browser. You’ll get a detailed HTML comparison report between the two workload replays.

With this, you’ll never waste another hour wrestling with RAT comparison report generation. It’s chunked, clean, and works every time – no more ORA-06502 headaches.