Using DML Commands on NESTED TABLES Within PL/SQL - Oracle

Just like in SQL, we can also insert, update or delete NESTED TABLE information from within PL/SQL, but using typed variables. Let us consider the following example which updates a row in the same table.

declare
    v_subjects SubjectList;
    v_name employee.name%type := '&name';
begin
    v_subjects := SubjectList('all databases','all languages');
    update employee set Subjects = v_subjects
    where name=v_name;
    dbms_output.put_line('Updated succesfully...');
end;

I hope the example above is very much clear. And even in the same manner we can also insert into the same table something like the following:

declare
    v_subjects SubjectList;
    v_name employee.name%type := '&name';
begin
    v_subjects := SubjectList('all databases','all languages');
    insert into employee values(v_name, v_subjects);
    dbms_output.put_line('inserted succesfully...');
end;

Actually, it is bit difficult to do some kinds of DML operations on NESTED TABLEs (and on the individual elements within the NESTED TABLE especially). Though I am not covering much of the SQL here, we can overcome the complexity with vigorous practice of SQL.