Old code I'm trying to fix up - can someone assist? It gives me a strange ORA-01403: no data found in PL/SQL
Details:
------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
------------------------------------------------------------------
set linesize 600
set serveroutput on
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET verify OFF
SET echo OFF
SPOOL "C:\stuff\test.123.sql";
DECLARE
CURSOR c_instance
IS
SELECT instance_number, instance_name
FROM gv$instance
ORDER BY 1;
v_dbid v$database.dbid%TYPE;
v_dbname v$database.name%TYPE;
v_inst_num v$instance.instance_number%TYPE := 1;
loop_num NUMBER;
v_max NUMBER;
v_begin NUMBER;
v_end NUMBER;
v_start_date VARCHAR2 (20);
v_end_date VARCHAR2 (20);
v_options NUMBER := 8; -- 0=no options, 8=enable addm feature
v_file UTL_FILE.file_type;
v_file_name VARCHAR (50);
PROCEDURE printSQL ( vpath varchar2,
vinstanceName varchar2,
vbegin varchar2,
vend varchar2,
vstart_date varchar2,
vend_date varchar2,
vdbid varchar2,
vinstance_number varchar2,
voptions varchar2
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('
SPOOL ' || vpath || vinstanceName || '_' || vbegin || '_' || vend || '_' || vstart_date || '_' || vend_date || '_RMETRICS.html ' || CHR (10) || '
BEGIN' || CHR (10) || '
FOR c_report' || CHR (10) || '
IN (' || CHR (10) || '
SELECT output' || CHR (10) || '
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html (' || CHR (10) || '
' || vdbid ||',' || CHR (10) || '
' || vinstance_number ||',' || CHR (10) || '
' || vbegin ||',' || CHR (10) || '
' || vend ||',' || CHR (10) || '
' || voptions ||' ' || CHR (10) || '
))' || CHR (10) || '
)' || CHR (10) || '
LOOP' || CHR (10) || '
DBMS_OUTPUT.PUT_LINE (c_report.output);' || CHR (10) || '
END LOOP;' || CHR (10) || 'END;'
|| CHR (10)
|| CHR (10) || '/'
);
END printSQL;
BEGIN
-- get database id
SELECT dbid, name
INTO v_dbid, v_dbname
FROM v$database;
-- get end snapshot id
SELECT MAX (snap_id)
INTO v_max
FROM dba_hist_snapshot
WHERE TO_CHAR (END_INTERVAL_TIME, 'yyyy/mm/dd hh24') <= to_char(sysdate,'yyyy/mm/dd hh24');
DBMS_OUTPUT.PUT_LINE ('
set linesize 550' || CHR (10) || '
set serveroutput on' || CHR (10) || '
SET FEEDBACK OFF' || CHR (10) || '
SET TERMOUT OFF' || CHR (10) || '
SET TRIMSPOOL ON' || CHR (10) || '
SET TRIMOUT ON' || CHR (10) || '
SET verify OFF' || CHR (10) || '
SET echo OFF' || CHR (10)
);
-- let's go to real work...write awrs to files...
FOR v_instance IN c_instance
LOOP
--dbms_output.put_line(chr(10));
--DBMS_OUTPUT.put_line ('v_instance.instance_name:' || v_instance.instance_name);
-- begin 1 hour x 24 --
--dbms_output.put_line(chr(10));
--DBMS_OUTPUT.PUT_LINE ('================================ begin 1 hour x 24 ================================');
--FOR loop_num IN 0..23 LOOP
FOR loop_num IN 0..23 LOOP
v_end := v_max - loop_num;
v_begin := v_max - loop_num - 1;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin AND instance_number = v_inst_num;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end AND instance_number = v_inst_num;
printSQL ('C:\stuff\',
v_instance.instance_name,
v_begin,
v_end,
v_start_date,
v_end_date,
v_dbid,
v_instance.instance_number,
v_options
);
END LOOP;
-- end 1 hour x 24 --
--begin 12 hours--
v_end := v_max;
v_begin := v_max - 12;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin AND instance_number = v_inst_num;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end AND instance_number = v_inst_num;
printSQL ('C:\stuff\',
v_instance.instance_name,
v_begin,
v_end,
v_start_date,
v_end_date,
v_dbid,
v_instance.instance_number,
v_options
);
-- end 12 hours --
--begin 12 hours 02--
v_end := v_max - 12;
v_begin := v_max - 24;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin AND instance_number = v_inst_num;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end AND instance_number = v_inst_num;
printSQL ('C:\stuff\',
v_instance.instance_name,
v_begin,
v_end,
v_start_date,
v_end_date,
v_dbid,
v_instance.instance_number,
v_options
);
-- end 12 hours --
-- begin 24 hours ---
v_end := v_max;
v_begin := v_max - 24;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin AND instance_number = v_inst_num;
SELECT TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end AND instance_number = v_inst_num;
printSQL ('C:\stuff\',
v_instance.instance_name,
v_begin,
v_end,
v_start_date,
v_end_date,
v_dbid,
v_instance.instance_number,
v_options
);
-- end 24 hours ---
END LOOP;
DBMS_OUTPUT.PUT_LINE ('
EXIT;' );
end;
/
EXIT;