Update a table which has a nested table

Question

My application has users (and subtype applicant) and jobs, with applicants being able to submit applications for jobs. I have objects types and tables set up as below :

create or replace type user_typ as object(
  user_id number(19,0),
  username nvarchar2(40 char)
)NOT FINAL;

I inherit an applicant_typ from this :

create or replace type applicant_typ under user_typ (
  resume_text nclob
);

My design involves jobs to which applicants can apply. To this end, I create an application_typ as follows :

create or replace TYPE Application_typ AS OBJECT (
  application_id NUMBER,
  candidate applicant_typ,
  time_of_app DATE
);

CREATE TYPE Application_tab IS TABLE OF Application_typ; 

And now I create an object type called Job_typ, and a table containing those objects, wherein there is a nested table for applications :

CREATE OR REPLACE TYPE Job_typ AS OBJECT (
  job_ID NUMBER,
  description NVARCHAR2(1000),
  name NVARCHAR2(200),
  application Application_tab
);

CREATE TABLE Job_tab OF Job_typ 
NESTED TABLE application STORE AS application_nt;

All of this works fine. I inserted some jobs into the job_tab table as follows :

INSERT INTO job_tab VALUES (1, 'The Software Developer will be responsible for authoring high-quality software...', 
  'Software Developer', NULL);

INSERT INTO job_tab VALUES (2, 'This position requires a team player and a self-starter with experience leading... ',
  'Project Manager', NULL);

INSERT INTO job_tab VALUES (3, 'In the first year of this unique 24-month program, you rotate through various...', 
  'Store Manager', NULL);

Note that at this point the nested table attribute 'application' has NULL value for all jobs. Assume that I already have some applicants in a table called applicant_table, which are of type applicant_typ. How do I create and insert a new application for a job?

I tried some things like :

UPDATE job_tab
SET application = application_typ (
  1,  
  Applicant_typ(SELECT VALUE(a) from applicant_table a where user_id=1),  
  '12-MAY-2014') 
WHERE job_id=1

But this doesn't work.

Answer 1

Consider something like this

merge into job_tab D
using (select t.a app_typ from applicant_table t where t.a.user_id = 1) S
on (job_id = 1)
when matched then
   update
      set application = Application_tab(Application_typ(1,
                                                        s.app_typ,
                                                        to_date('12-05-2014', 'DD-MM-YYYY')));

Also I will suggest to use some other naming convention, very complicated to understand at first glance.