CREATE TABLE stg_account (
  acc_id   NUMBER,
  balance  NUMBER,
  status   VARCHAR2(1)
);

TRUNCATE TABLE stg_account;

INSERT INTO stg_account (acc_id, balance, status) VALUES (1001, 1000, 'A'); -- valid
INSERT INTO stg_account (acc_id, balance, status) VALUES (1002,  200, 'B'); -- valid
INSERT INTO stg_account (acc_id, balance, status) VALUES (1003,  -50, 'A'); -- CHECK violation
INSERT INTO stg_account (acc_id, balance, status) VALUES (1004,  500, NULL); -- NOT NULL violation
INSERT INTO stg_account (acc_id, balance, status) VALUES (1001,  300, 'A'); -- PK violation (duplicate in target once 1001 is inserted)

COMMIT;

CREATE TABLE dwh_account (
  acc_id   NUMBER PRIMARY KEY,
  balance  NUMBER CHECK (balance >= 0),
  status   VARCHAR2(1) NOT NULL
);
--Create error log:

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('DWH_ACCOUNT');
END;
/
--Package 
CREATE OR REPLACE PACKAGE p_account_load AS

  PROCEDURE load_accounts(p_batch_id NUMBER);

END p_account_load;
--Package Body
CREATE OR REPLACE PACKAGE BODY p_account_load AS

  PROCEDURE load_accounts(p_batch_id NUMBER) IS
  BEGIN
    INSERT INTO dwh_account (
      acc_id,
      balance,
      status
    )
    SELECT
      acc_id,
      balance,
      status
    FROM stg_account
    LOG ERRORS INTO err$_dwh_account (
      'BATCH=' || p_batch_id
    )
    REJECT LIMIT UNLIMITED;

    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      -- Only statement-level exceptions arrive here
      ROLLBACK;
      RAISE;
  END load_accounts;

END p_account_load;
/

BEGIN
  p_account_load.load_accounts(2);
END;

SELECT * FROM dwh_account;
SELECT * FROM err$_dwh_account;
