Batch generation of awr scripts by stored procedures

DECLARE

L_snap_start NUMBER: = 40078

L_snap_end NUMBER: = 40081

L_dir VARCHAR2 (50): = 'AWRTEST'

L_last_snap NUMBER: = NULL

L_dbid NUMBER: = 2778659381

L_file UTL_FILE.file_type

L_file_name VARCHAR (50)

Cursor cur_inum is SELECT instance_number FROM dba_hist_snapshot WHERE dbid = l_dbid GROUP BY instance_number ORDER BY instance_number

BEGIN

For l_instance_number in cur_inum loop

L_last_snap: = NULL

FOR cur_snap IN (SELECT snap_id

FROM dba_hist_snapshot

WHERE instance_number = l_instance_number.instance_number

AND snap_id BETWEEN l_snap_start AND l_snap_end

ORDER BY snap_id)

LOOP

IF l_last_snap IS NOT NULL THEN

L_file: = UTL_FILE.fopen (l_dir, 'awr_' | | l_instance_number.instance_number | |' _'| | l_last_snap | |'_'| | cur_snap.snap_id | | '.html', 'wicked, 32767);-- 40162

FOR cur_rep IN (SELECT output

FROM TABLE (DBMS_WORKLOAD_REPOSITORY.awr_report_html (l_dbid, l_instance_number.instance_number, l_last_snap, cur_snap.snap_id))

LOOP

UTL_FILE.put_line (l_file, cur_rep.output)

END LOOP

UTL_FILE.fclose (l_file)

END IF

L_last_snap: = cur_snap.snap_id;-- 40161

END LOOP

End loop

EXCEPTION

WHEN OTHERS THEN

IF UTL_FILE.is_open (l_file) THEN

UTL_FILE.fclose (l_file)

END IF

RAISE

END