oracle-databaseplsqlobject-relational-model

ORA-00927: missing equal sign when trying to update table in object-relational DB


i'm trying to understand object-relational technology and created parent type:

CREATE OR REPLACE TYPE STUDENT
AS OBJECT(
    FIO VARCHAR2(200),
    Bday DATE,
    Pas_Id NUMBER(20),
    Address VARCHAR2(50))
NOT INSTANTIABLE
NOT FINAL;

child type:

CREATE OR REPLACE TYPE BACH
UNDER STUDENT(
    fieldOfStud varchar2(200),
    Group_N number(4),
    ege number(4),
    GPA number(4),
    MEMBER FUNCTION year_N RETURN NUMBER);

--тело типа бакалавр
CREATE OR REPLACE TYPE BODY BACH IS
MEMBER FUNCTION year_N RETURN NUMBER IS
    BEGIN
        IF SUBSTR(Group_N, 1, 1) BETWEEN 1 and 4 THEN
            RETURN SUBSTR(Group_N, 1, 1);
        END IF;
        RETURN 0;
    END;
END;

created table:

CREATE TABLE Students_Table(
SID  NUMBER CONSTRAINT id_pk PRIMARY KEY,
stdt STUDENT
);

and inserted data. I am 100% sure it exists. I want to update table and change group, for example, from 5231 to 6231:

update Students_Table st 
set    TREAT(stdt as maga).Group_N = TREAT(stdt as maga).Group_N+1000 
where  TREAT(stdt AS MAGA).Pas_Id = 241122

but get error

ORA-00927: missing equal sign

Solution

  • You need to apply the conversion earlier in the statement, so that the update only sees the subtype:

    update ( select s.sid, treat(stdt as bach) as stdt from students_table s ) st 
    set    st.stdt.group_n = st.stdt.group_n + 1000 
    where  st.stdt.pas_id = 241122;
    

    DB Fiddle