databaseoracle-databaseplsqlobject-relational-model

How to update subtype attribute values in oracle database?

How to update column value of a subtype?

Code like this can't access the grade attribute of student:

update persons set grade = 'graduated';

CREATE OR REPLACE TYPE person (
    name varchar2(20),
    age number,
    address varchar2(20)
) NOT FINAL;

CREATE OR REPLACE TYPE student UNDER person (
    grade varchar2(20)
) NOT FINAL;

CREATE TABLE persons OF person;

INSERT INTO persons VALUES (student('Jon', 'undergraduate'));

How I change student grade to 'graduated'?

Solution

  • I found a answer to my problem.
    This is the code to update subtype attributes:

    DECLARE
    s student;

BEGIN
    select treat(value(p) as student) into s from persons p where name = 'Jon';
    s.grade = 'graduated';
    update persons p set value(p) = s where name = 'Jon';
END;