Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE TYPE CAR_T AS OBJECT (
  id VARCHAR (10),
  fuel_type VARCHAR (10)
) NOT FINAL;
CREATE OR REPLACE TYPE SUPERCAR_T UNDER CAR_T (
  number_cylinders  number(10)
);
CREATE OR REPLACE TYPE OTHER_CAR_T UNDER CAR_T (
  other_thing  number(10)
);
CREATE TABLE car of car_t;
CREATE OR REPLACE TRIGGER INSERT_SUPERCAR
  BEFORE INSERT ON CAR
  REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  IF :NEW.OBJECT_VALUE IS OF (SUPERCAR_T)
      AND TREAT(:NEW.OBJECT_VALUE AS SUPERCAR_T).number_cylinders < 6
  THEN
    RAISE_APPLICATION_ERROR(-20001,'Not a supercar');
  END IF;
END;
/
SELECT * FROM USER_ERRORS;
INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 12));
1 rows affected

INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 4));
ORA-20001: Not a supercar
ORA-06512: at "FIDDLE_YJBPISWDWYDHBNHHKSAN.INSERT_SUPERCAR", line 5
ORA-04088: error during execution of trigger 'FIDDLE_YJBPISWDWYDHBNHHKSAN.INSERT_SUPERCAR'

INSERT INTO CAR VALUES(OTHER_CAR_T(100, 'petrol', 4));
1 rows affected

SELECT c.*,
       TREAT(c.OBJECT_VALUE AS SUPERCAR_T).number_cylinders AS number_cylinders
FROM   car c


ID
FUEL_TYPE
NUMBER_CYLINDERS


99
petrol
12

100
petrol
null


ALTER TABLE car ADD CONSTRAINT car__supercar_cylinders__chk CHECK (
  OBJECT_VALUE IS NOT OF (SUPERCAR_T)
  OR TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders >= 6
);
DROP TRIGGER INSERT_SUPERCAR;
INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 4));
ORA-02290: check constraint (FIDDLE_YJBPISWDWYDHBNHHKSAN.CAR__SUPERCAR_CYLINDERS__CHK) violated

ALTER TABLE car ADD CONSTRAINT car__supercar_cylinders2__chk CHECK (
  TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders IS NULL
  OR TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders >= 6
);
INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 4));
ORA-02290: check constraint (FIDDLE_YJBPISWDWYDHBNHHKSAN.CAR__SUPERCAR_CYLINDERS2__CHK) violated

INSERT INTO CAR VALUES(OTHER_CAR_T(100, 'petrol', 4));
1 rows affected