emcc 捕获到的一个 plsql 匿名块

os: centos 7.6
db: oracle 19.3
emcc: 13

使用 emcc 13 捕获到的一个 plsql 匿名块,分享下.

-- ============================================================================================== --
-- This file is generated by EM Database Express.			                          --
-- The SQL bind variables, if any, will be declared below.                                        --
-- ============================================================================================== --

DECLARE

	l_last_complete_disk_backup    VARCHAR2(64);
	l_disk_recovery_window         NUMBER;
	l_disk_unprotected_data_window NUMBER;

	l_last_complete_sbt_backup     VARCHAR2(64);
	l_sbt_recovery_window          NUMBER;
	l_sbt_unprotected_data_window  NUMBER;

	l_zdlra_media_count            NUMBER := 0;

	TYPE data_cursor_type IS REF CURSOR;
	data_cursor data_cursor_type;
	
	v_db_version                   VARCHAR2(10);
	db_version_121                 CONSTANT VARCHAR2(10) := '12.1.0.0.0';
	
BEGIN
	SELECT LPAD(version, 10, '0') 
	  INTO v_db_version
	  FROM v$instance;

	SELECT COUNT (UNIQUE media)
	INTO l_zdlra_media_count
	FROM v$backup_piece_details p
		 ,(SELECT session_key
				, session_recid
				, session_stamp
			FROM (SELECT session_key
						, session_recid
						, session_stamp
					FROM v$rman_backup_job_details
					WHERE status LIKE 'COMPLETED%'
					  AND output_device_type IN ('SBT_TAPE', '*')
					ORDER BY end_time DESC)
			WHERE rownum = 1) j
	WHERE j.session_key = p.session_key
	AND j.session_recid = p.session_recid
	AND j.session_stamp = p.session_stamp
	AND p.media like '%Recovery Appliance%';

	BEGIN
		IF (v_db_version >= db_version_121) THEN
			EXECUTE IMMEDIATE
			'SELECT to_char(low_time, ''YYYY-MM-DD HH24:MI:SS'') as last_complete_disk_backup,
					ROUND((high_time-low_time)*24*60*60, 2) as disk_recovery_window,
					ROUND((sysdate-high_time)*24*60*60, 2) as disk_unprotected_data_window
			   FROM v$disk_restore_range rr1
			  WHERE rr1.high_time = (SELECT max(rr2.high_time)
					  	   	           FROM v$disk_restore_range rr2
									  WHERE rr1.db_id = rr2.db_id)'
			INTO l_last_complete_disk_backup,
			     l_disk_recovery_window, 
				 l_disk_unprotected_data_window;
				 
		END IF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			l_last_complete_disk_backup := NULL;
			l_disk_recovery_window := NULL;
			l_disk_unprotected_data_window := NULL;
		WHEN OTHERS THEN
			dbms_output.put_line(SQLERRM);
	END;

	BEGIN
		IF (v_db_version >= db_version_121 AND l_zdlra_media_count = 0) THEN
			EXECUTE IMMEDIATE
			'SELECT to_char(low_time, ''YYYY-MM-DD HH24:MI:SS'') as last_complete_sbt_backup,
					ROUND((high_time-low_time)*24*60*60, 2) as sbt_recovery_window,
					ROUND((sysdate-high_time)*24*60*60, 2) as sbt_unprotected_data_window
			   FROM v$sbt_restore_range rr1
			  WHERE rr1.high_time = (SELECT max(rr2.high_time)
									   FROM v$sbt_restore_range rr2
									  WHERE rr1.db_id = rr2.db_id)'
			INTO l_last_complete_sbt_backup,
			     l_sbt_recovery_window, 
				 l_sbt_unprotected_data_window;
				 
		END IF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			l_last_complete_sbt_backup := NULL;
			l_sbt_recovery_window := NULL;
			l_sbt_unprotected_data_window := NULL;
		WHEN OTHERS THEN
			dbms_output.put_line(SQLERRM);
	END;

	OPEN data_cursor FOR
	SELECT l_last_complete_disk_backup, 
		   l_disk_recovery_window,
		   l_disk_unprotected_data_window, 
		   l_last_complete_sbt_backup,
		   l_sbt_recovery_window, 
		   l_sbt_unprotected_data_window
	FROM dual;
	
	:1   := data_cursor;
END;

参考: