Live ORACLE

DROP TABLE department CASCADE CONSTRAINTS PURGE
/

DROP TYPE CourseList
/

DROP TYPE Course
/

CREATE TYPE Course AS OBJECT
(course_no NUMBER,
title VARCHAR2(64),
credits NUMBER);
/

CREATE TYPE CourseList AS TABLE OF course;
/

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab
/

INSERT INTO department VALUES ('English', 'June Johnson', '491C',
CourseList(
Course(1002, 'Expository Writing', 4),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 4)))
/

DECLARE
revised CourseList :=
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*) INTO num_changed
FROM TABLE(CAST(revised AS CourseList)) new,
TABLE(SELECT courses FROM department
WHERE name = 'English') old
WHERE new.course_no = old.course_no AND
(new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/

Performing Operations on PL/SQL Nested Tables With CAST