RTTI in Oracle Triggers

Question

I have this dummy types :

create or replace type Service_TY as object(
  code INTEGER,
  visit_analysis char(1)
)FINAL;
/

create or replace type Employee_TY as object(
   dummy varchar(30)
)NOT FINAL;
/

create or replace type Doctor_TY UNDER Employee_TY(
  ID INTEGER
)FINAL;
/

create or replace type Assistant_TY UNDER Employee_TY(
  ID INTEGER
)FINAL;
/

create or replace type Habilitation_TY as object(
  employee ref Employee_TY,
  service ref Service_TY
)FINAL;
/

And these dummy tables:

CREATE TABLE Service of Service_TY(
  code primary key,
  visit_analysis not null check (visit_analysis in ('v', 'a'))
);
/

CREATE TABLE Doctor of Doctor_TY(
  ID primary key
);
/

CREATE TABLE Assistant of Assistant_TY(
  ID primary key
);
/

CREATE TABLE Habilitation of Habilitation_TY;
/

I want to create a trigger that, when a new tuple is inserted in Habilitation, should check that, if the employee is an assistant (and not a doctor), the visit_analysis attribute is equal to 'a' to know if it is a legal tuple.

I don't know how to check the type of the Employee (if it is a doctor or an assistant).

I would do something like that:

create or replace
TRIGGER CHECK_HABILITATION
BEFORE INSERT ON HABILITATION
FOR EACH ROW
DECLARE
BEGIN
    IF (:NEW.EMPLOYEE is of ASSISTANT_TY)
    THEN
      IF :NEW.SERVICE.visit_analysis = 'v'
         THEN
             raise_application_error(-10000, 'invalid tuple');
    END IF;
END;

But it's not working. How should I check that type? The error I get is: Error(14,4): PLS-00103: Encountered the symbol ";" when expecting one of the following: if

I notice that you don't have a `/` following the `CREATE TRIGGER` statement, unlike all your other statements. Did you actually execute it and create the trigger? — Dave Costa, Jan 25 '21 at 16:18
yes I have executed it, the compiler tells me some random errors on the IF statement — juuso, Jan 25 '21 at 16:20
Error(14,4): PLS-00103: Encountered the symbol ";" when expecting one of the following: if — juuso, Jan 25 '21 at 16:21
It would be good to edit your question to include the error message. — Dave Costa, Jan 25 '21 at 16:24

score 1 · Accepted Answer · answered Jan 25 '21 at 18:42

Try to put it into a variable, the following one should work.

create or replace
TRIGGER CHECK_HABILITATION
BEFORE INSERT ON HABILITATION
FOR EACH ROW
DECLARE
  emp employee_TY;
  ser service_TY;
BEGIN
  select deref(:new.employee) into emp from dual;
  if (emp is of (assistant_ty)) then
    select deref(:new.service) into ser from dual;
    if ser.visit_analysis = 'v' then
      raise_application_error('-20001', 'invalid tuple');
    end if;
  end if;
END;
/

score 0 · Answer 2 · answered Jan 25 '21 at 16:31

0

According to the documentation for the IS OF condition, you need to wrap the type in parentheses, like:

IF (:NEW.EMPLOYEE is of (ASSISTANT_TY) )

per https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions014.htm#SQLRF52157.

I'm not really familiar with using object types so there may be some other issue that I'm not seeing.

answered Jan 25 '21 at 16:31

Dave Costa

47,262
8
56
72

I still get the same issue, the errors from the compiler are not helping at all and the oracle documentation seems to avoid to talk about objects and how to treat them – juuso Jan 25 '21 at 16:32

RTTI in Oracle Triggers

2 Answers2