Oracle 18c

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

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

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