Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dept
(
  dept_id    number(3) not null,
  dept_name  varchar2(50) not null,
  emp_cnt    number(3) not null,
  constraint dept_pk primary key (dept_id)
);
CREATE TABLE employees
(
  emp_id   number(3) not null,
  dept_id  number(3) not null,
  emp_name varchar2(50) not null,
  address  varchar2(100),
  phone    varchar2(20) not null,
  salary   number(8,2) not null,
  constraint emp_pk primary key (emp_id),
  constraint dept_fk foreign key (dept_id) references dept(dept_id)
);
CREATE OR REPLACE TRIGGER trig_mask_phone
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
     IF LENGTH(:new.phone) < 10 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Phone No is incorrect');
     END IF;
     :new.phone := '(' || SUBSTR(:new.phone, 1, 3) || ')' || SUBSTR(:new.phone, 4, 3) || '-' || SUBSTR(:new.phone, 7, 2) || '-' || SUBSTR(:new.phone, 9, 2);
END;
/
INSERT INTO dept VALUES (1, 'A', 10);
1 rows affected

INSERT INTO employees VALUES (1, 1, 'a', 'abcd', '1234567890', 10000);
1 rows affected

INSERT INTO employees VALUES (1, 1, 'a', 'abcd', '123467890', 10000);
ORA-20001: Phone No is incorrect
ORA-06512: at "FIDDLE_CTYEWGNZLGOMUABJRUTN.TRIG_MASK_PHONE", line 3
ORA-04088: error during execution of trigger 'FIDDLE_CTYEWGNZLGOMUABJRUTN.TRIG_MASK_PHONE'

SELECT * FROM employees;


EMP_ID
DEPT_ID
EMP_NAME
ADDRESS
PHONE
SALARY


1
1
a
abcd
(123)456-78-90
10000