By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE t_ap_recovery_target (
a_recovery_target_month,
a_target_instance,
a_focus_area_id,
a_range_id,
A_IS_RECOVERED
) AS
SELECT '1', 1, 1, 1, 'Y' FROM DUAL UNION ALL
SELECT '1', 1, 1, 1, 'N' FROM DUAL UNION ALL
SELECT '1', 2, 1, 1, 'N' FROM DUAL UNION ALL
SELECT '1', 2, 1, 1, 'N' FROM DUAL;
4 rows affected
CREATE TABLE t_ap_recovery_focusarea (
id,
focus_area
) AS
SELECT 1, 1 FROM DUAL;
1 rows affected
CREATE TABLE range(
id,
identifier_class
) AS
SELECT 1, 1 FROM DUAL;
1 rows affected
CREATE PROCEDURE ADD_LOG_INFO(
x IN VARCHAR2,
y IN VARCHAR2
)
AS
BEGIN
NULL;
END;
/
CREATE SEQUENCE SQ_AP_RECOVERY_HISTORY;
CREATE TABLE t_ap_recovery_history (
ID NUMBER,
RECOVERY_TARGET_MONTH VARCHAR2(50),
TARGET_INSTANCE NUMBER,
RECOVERY_PROGRESS NUMBER,
RECOVERY_TARGET NUMBER,
FAILED_TO_RECOVERY NUMBER,
FOCUS_AREA NUMBER,
IDENTIFIER_CLASS NUMBER,
CREATED_ON DATE
);
CREATE PROCEDURE CALCULATE_RECOVERY_HISTORY(
p_month IN VARCHAR2
)
AS
BEGIN
ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');
INSERT INTO t_ap_recovery_history (
ID,
RECOVERY_TARGET_MONTH,
TARGET_INSTANCE,
RECOVERY_PROGRESS,
RECOVERY_TARGET,
FAILED_TO_RECOVERY,
FOCUS_AREA,
IDENTIFIER_CLASS,
CREATED_ON
)
SELECT SQ_AP_RECOVERY_HISTORY.NEXTVAL,
a_recovery_target_month,
a_target_instance,
RECOVERY_PROGRESS,
RECOVERY_TARGET,
FAILED_TO_RECOVERY,
focus_area,
identifier_class,
SYSDATE
FROM (
SELECT a_recovery_target_month,
a_target_instance,
COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END) AS RECOVERY_PROGRESS,
COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END) AS RECOVERY_TARGET,
COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END) AS FAILED_TO_RECOVERY,
f.focus_area,
r.identifier_class
FROM t_ap_recovery_target t
SELECT * FROM USER_ERRORS;
BEGIN
CALCULATE_RECOVERY_HISTORY('1');
END;
/
1 rows affected
SELECT * FROM t_ap_recovery_history;
| ID | RECOVERY_TARGET_MONTH | TARGET_INSTANCE | RECOVERY_PROGRESS | RECOVERY_TARGET | FAILED_TO_RECOVERY | FOCUS_AREA | IDENTIFIER_CLASS | CREATED_ON |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 2023-02-08 09:51:59 |
| 2 | 1 | 2 | 0 | 2 | 2 | 1 | 1 | 2023-02-08 09:51:59 |