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 OR REPLACE PROCEDURE report_init_sp
AS
table_exists EXCEPTION;
PRAGMA EXCEPTION_INIT (table_exists, -955);
BEGIN
BEGIN
EXECUTE IMMEDIATE q'[
CREATE TABLE sao_report_tbl(
id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
proc_name VARCHAR(100) NOT NULL,
proc_start TIMESTAMP NULL,
proc_end TIMESTAMP NULL,
proc_status VARCHAR(100) NULL,
proc_runtime NUMBER NULL,
row_count NUMBER NULL,
PRIMARY KEY (id)
)
]';
EXCEPTION
WHEN table_exists THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE sao_report_tbl';
END;
EXECUTE IMMEDIATE q'[
INSERT INTO sao_report_tbl(proc_name)
SELECT 'sao_eligible_members' FROM DUAL UNION ALL
SELECT 'sao_ffs_claim' FROM DUAL UNION ALL
SELECT 'sao_ffs_claim_notes' FROM DUAL
]';
END report_init_sp;
/
SELECT * FROM USER_ERRORS;
BEGIN
report_init_sp();
END;
/
1 rows affected
SELECT * FROM sao_report_tbl;
| ID | PROC_NAME | PROC_START | PROC_END | PROC_STATUS | PROC_RUNTIME | ROW_COUNT |
|---|---|---|---|---|---|---|
| 1 | sao_eligible_members | null | null | null | null | null |
| 2 | sao_ffs_claim | null | null | null | null | null |
| 3 | sao_ffs_claim_notes | null | null | null | null | null |
BEGIN
report_init_sp();
END;
/
1 rows affected
SELECT * FROM sao_report_tbl;
| ID | PROC_NAME | PROC_START | PROC_END | PROC_STATUS | PROC_RUNTIME | ROW_COUNT |
|---|---|---|---|---|---|---|
| 4 | sao_eligible_members | null | null | null | null | null |
| 5 | sao_ffs_claim | null | null | null | null | null |
| 6 | sao_ffs_claim_notes | null | null | null | null | null |