Procedure to create table or truncate it, and populate with data

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 when I try to drop it ([61000][54] ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Position: 11). 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;

>Solution :

Don’t catch OTHERS. Only catch the exception that you are expecting to be raised and let any other (unexpected) exceptions propagate.

If the table creation fails because the table already exists then truncate the table; you don’t need to truncate the table if you have only just created it.

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;
/

fiddle