I want a procedure that will 1) create a table if it doesn’t exist, 2) truncate the table if it does exist, 3) populate some data into the table.

I sort of have it, but it seems to take forever and keeps the table locked. What’s a better way to do this?

(I have omitted several of the inserts for brevity)

CREATE OR REPLACE PROCEDURE report_init_sp AS
BEGIN

    -- Create table, if not exists
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    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 OTHERS
            THEN RAISE_APPLICATION_ERROR( -20001, q'[Create table failed.]' );
    END;

    -- Truncate table
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE sao_report_tbl';
        EXCEPTION
    WHEN OTHERS
        THEN RAISE_APPLICATION_ERROR( -20001, q'[Truncate table failed.]' );
    END;

    -- Populate table with procedure names (proc_name)
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    BEGIN
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_eligible_members')
    ]';
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_ffs_claim')
    ]';
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_ffs_claim_notes')
    ]';
    EXCEPTION
        WHEN OTHERS
            THEN RAISE_APPLICATION_ERROR( -20001, q'[INSERT failed.]' );
    END;
END report_init_sp;