declare
  att cioattribute.data;        
  dom ciodomain.data;
  act_status varchar2(1);     -- Activity status
  act_warnings varchar2(1);   -- Activity warning flag

  v_app_id number(38);
  cursor get_application_id (p_app_name  varchar2,
                             p_app_version  number) is
     select id
     from ci_application_systems
     where name = p_app_name and
           version = p_app_version;
  unknown_application_system exception;
  pragma exception_init(unknown_application_system, -20103);

  cursor jmp_domains is
    select name             dom_name,
           format           dom_format,
           max_att_length   dom_max_att_length,
           avg_att_length   dom_avg_att_length,
           att_precision    dom_att_precision,
           datatype         dom_datatype,
           max_col_length   dom_max_col_length,
           avg_col_length   dom_avg_col_length,
           col_precision    dom_col_precision,
           description      dom_description
    from   jump_domains dom;
  cur_jump_domain jmp_domains%rowtype;

  cursor matching_domain (p_name varchar2,
                          p_app_id number) is
     select dom.id dom_id
     from ci_domains dom
     where dom.name = p_name and
           dom.application_system_owned_by = p_app_id;
  matching_dom_id ci_domains.id%type;

  procedure instantiate_messages is
    m_facility varchar2(3);
    m_code number;
    arg1 varchar2(240);
    arg2 varchar2(64);
    arg3 varchar2(64);
    arg4 varchar2(64);
    arg5 varchar2(20);
    arg6 varchar2(20);
    arg7 varchar2(20);
    arg8 varchar2(20);
  begin
    -- Report all violations regardless of the activity status
    for viol in (select * from ci_violations) loop
      dbms_output.put_line( cdapi.instantiate_message(
         viol.facility,viol.code,
         viol.p0,viol.p1,viol.p2,
         viol.p3,viol.p4,viol.p5,
         viol.p6,viol.p7 ) );
    end loop;
    -- Pop messages off the stack and format them into 
    -- a single text string
    while cdapi.stacksize > 0 loop
      rmmes.pop( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,
                                   arg6,arg7,arg8);
      dbms_output.put_line(cdapi.instantiate_message
          ( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8));
    end loop;
  end;

begin

  -- Get Application ID
  open get_application_id(upper('&&app_name'),&&app_version);
  fetch get_application_id into v_app_id;
  if get_application_id%notfound then
    raise_application_error(-20103,
         'Sorry, the application that you have entered is unknown.');
  end if;
  close get_application_id;
 

  -- Initialize API if not already done
  if cdapi.initialized = false then
    -- Initialize the API globals
    cdapi.initialize(upper('&&app_name'), &&app_version);
  end if;

  -- Set DBMS Output Buffer to Max Size
  dbms_output.enable(1000000);

  for cur_jump_domain in jmp_domains loop
    -- Look for a domain with the same name
    open matching_domain(cur_jump_domain.dom_name,v_app_id);
    fetch matching_domain into matching_dom_id;

    -- If there is no such domain already, create one.
    if matching_domain%notfound then
      cdapi.open_activity;
      dom.v.application_system_owned_by := v_app_id;
      dom.i.application_system_owned_by := TRUE;
      dom.v.name := cur_jump_domain.dom_name;
      dom.i.name := TRUE;
      dom.v.format := cur_jump_domain.dom_format;
      dom.i.format := TRUE;
      dom.v.maximum_attribute_length := cur_jump_domain.dom_max_att_length;
      dom.i.maximum_attribute_length := TRUE;
      dom.v.average_attribute_length := cur_jump_domain.dom_avg_att_length;
      dom.i.average_attribute_length := TRUE;
      dom.v.attribute_precision := cur_jump_domain.dom_att_precision;
      dom.i.attribute_precision := TRUE;
      dom.v.datatype := cur_jump_domain.dom_datatype;
      dom.i.datatype := TRUE;
      dom.v.maximum_column_length := cur_jump_domain.dom_max_col_length;
      dom.i.maximum_column_length := TRUE;
      dom.v.average_column_length := cur_jump_domain.dom_avg_col_length;
      dom.i.average_column_length := TRUE;
      dom.v.column_precision := cur_jump_domain.dom_col_precision;
      dom.i.column_precision := TRUE;
      dom.v.description := cur_jump_domain.dom_description;
      dom.i.description := TRUE;
      ciodomain.ins(null,dom);
      -- Validate the update
      cdapi.validate_activity(act_status,act_warnings);
      -- Get feedback
      instantiate_messages;
      cdapi.close_activity(act_status);
      -- If the activity did not close successfully, roll back
      -- all changes made during the activity
      if act_status != 'Y' then
        cdapi.abort_activity;
        dbms_output.put_line('Activity aborted with constraint violations');
      else
        dbms_output.put_line(cur_jump_domain.dom_name);
      end if;
    end if;
    close matching_domain;
  end loop;
exception
  when unknown_application_system then
    dbms_output.put_line('Sorry, the application that you have entered is unknown.');
    cdapi.abort_activity;
  when others then
    -- If any messages have been posted on the stack, then print them now
    -- and then roll back all changes made during the activity
    if cdapi.stacksize > 0 then
      -- Print all messages on the API stack
      while cdapi.stacksize > 0 loop
 dbms_output.put_line(cdapi.pop_instantiated_message);
      end loop;
      if cdapi.activity is not null then
        cdapi.abort_activity;
        dbms_output.put_line('Activity aborted with API errors');
      else
        dbms_output.put_line('API Session aborted with API errors');
      end if;

    -- Otherwise, this must have been an ORACLE SQL or internal error so
    -- roll back all changes made during the activity and re-raise the 
    -- exception
    else
      if cdapi.activity is not null then
        cdapi.abort_activity;
        dbms_output.put_line('Activity aborted with ORACLE internal errors');
      else
        dbms_output.put_line('API Session aborted with ORACLE internal errors');
      end if;
      raise;
    end if;
END;
/