-- Create table
      EXECUTE IMMEDIATE q'[CREATE TABLE dbzuser.poa (
         CARTON_NO             NUMBER(20,0)      NOT NULL,
         CARTON_SUFFIX_ID_NO   NUMBER(3,0)       NOT NULL,
         POA_DATE              DATE              NOT NULL,
         LAST_UPDATE_DATETIME  DATE              NOT NULL,
         USERNAME              VARCHAR2(15 BYTE) NOT NULL,
         LAST_UPDATE_USER      VARCHAR2(15 BYTE),
         TRANSPORTER_ID_NO     NUMBER(2,0)       NOT NULL,
         WEIGHT                NUMBER(5,2),
         POA_TIME              TIMESTAMP WITH TIME ZONE,
         DELIVERY_DATE         DATE,
         CONSTRAINT pk_poa PRIMARY KEY (CARTON_NO, CARTON_SUFFIX_ID_NO)
      )]';
      DBMS_OUTPUT.PUT_LINE('Iteration ' || j || ': Table recreated.');

      EXECUTE IMMEDIATE 'ALTER TABLE dbzuser.poa ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS';
      DBMS_OUTPUT.PUT_LINE('Iteration ' || j || ': Supplemental logging added.');
      
      --Insert records (session 1)
   
   CREATE SEQUENCE dbzuser.poa_seq
   START WITH 1
   INCREMENT BY 1
   NOCACHE;

  
  DECLARE
   v_batch_count  NUMBER := 10;  -- Total number of batches (adjust as necessary)
   v_row_count    NUMBER := 1000;
BEGIN
   FOR batch IN 1 .. v_batch_count LOOP
      FOR i IN 1 .. v_row_count LOOP
         INSERT INTO dbzuser.poa (
            CARTON_NO, 
            CARTON_SUFFIX_ID_NO,
            POA_DATE,
            LAST_UPDATE_DATETIME,
            USERNAME,
            LAST_UPDATE_USER,
            TRANSPORTER_ID_NO,
            WEIGHT,
            POA_TIME,
            DELIVERY_DATE
         ) VALUES (
            dbzuser.poa_seq.NEXTVAL,    -- Unique number generated by the sequence
            MOD(i, 10),                 -- Example carton suffix (0-9)
            SYSDATE,                    -- Using current date for POA_DATE
            SYSDATE,                    -- LAST_UPDATE_DATETIME as current date
            'INSERT_USER',              -- Example username
            NULL,                       -- LAST_UPDATE_USER is optional
            99,                         -- Example transporter id
            ROUND(DBMS_RANDOM.VALUE(1, 9999)/100, 2),  -- Random weight
            SYSTIMESTAMP,               -- Use current timestamp with timezone
            SYSDATE+MOD(i, 30)          -- Sample delivery date (varies by row)
         );
      END LOOP;
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Inserted batch ' || batch || ' (' || v_row_count || ' rows)');
      -- Pause for 10 seconds before next batch (simulate delay)
      DBMS_LOCK.sleep(10);
   END LOOP;
END;

-- add or remove column
DECLARE
   v_count INTEGER;
BEGIN
   -- Check if the column NEW_COLUMN exists on the table POA
   SELECT COUNT(*)
     INTO v_count
     FROM all_tab_columns
    WHERE owner = 'DBZUSER'
      AND table_name = 'POA'
      AND column_name = 'NEW_COLUMN';

   IF v_count = 0 THEN
      -- Column does not exist, so add it.
      EXECUTE IMMEDIATE 'ALTER TABLE dbzuser.poa ADD (new_column VARCHAR2(50) DEFAULT ''NEW_VALUE'')';
      DBMS_OUTPUT.PUT_LINE('Column NEW_COLUMN added.');
   ELSE
      -- Column exists, so drop it.
      EXECUTE IMMEDIATE 'ALTER TABLE dbzuser.poa DROP COLUMN new_column';
      DBMS_OUTPUT.PUT_LINE('Column NEW_COLUMN dropped.');
   END IF;
END;