Designer 2000 API

Creating First Cut Domains for all Attributes

By Hervé Deschamps.

You may use this utility if you find yourself with an application with no (or few) domains defined, yet with a significant number of attributes and columns already in there. This utility basically goes through all domainless attributes and either:

We recommend that you use domains against most attributes, especially: This utility is not very selective. It creates a domain for everything. That produces a lot a domains. If you do not like this, you can tweak the code a little bit to be more selective. For example, it would be very easy to create domains for UID(1) attributes only. If you do not feel confortable with doing it yourself, send me an email. If you're lucky, I'll do it for you.
set serveroutput on
set verify off
rem ******************************************************
rem * 
rem * This utility creates a domain for all attributes
rem * of an application in Designer 2000.
rem *
rem * For all domainless attributes:
rem *   - if a domain with the same name exists,
rem *        assign the attribute to this domain
rem *   - if not,
rem *        create a domain with the attribute name,
rem *        assign the attribute to this domain.
rem *
rem * Written by: H. Deschamps, 7/1/97.
rem * Last Modified: 7/1/97.
rem ******************************************************

  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 no_dom_attributes (p_app_id number) is
    select att_id,
    from   ci_attributes att, 
           ci_entities ent
    where  ent.application_system_owned_by = p_app_id and
  = att.entity_reference and
           att.domain_reference is null;
  cur_no_dom_attribute no_dom_attributes%rowtype;

  cursor matching_domain (p_name varchar2,
                          p_app_id number) is
     select dom_id,
            dom.format dom_format,
            dom.average_attribute_length dom_avg_att_len,
            dom.maximum_attribute_length dom_max_att_len,
            dom.attribute_precision dom_att_prec
     from ci_domains dom
     where = p_name and
           dom.application_system_owned_by = p_app_id;
  cur_matching_dom matching_domain%rowtype;
  domain_to_create varchar2(40);

  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);
    -- Report all violations regardless of the activity status
    for viol in (select * from ci_violations) loop
      dbms_output.put_line( cdapi.instantiate_message(
         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,
          ( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8));
    end loop;


  -- 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
         '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

  for cur_no_dom_attribute in no_dom_attributes(v_app_id) loop
    -- next domainless attribute
    cioattribute.sel(cur_no_dom_attribute.att_id ,att);

    -- Look for a domain with the same name as the attribute
    open matching_domain(,v_app_id);
    fetch matching_domain into cur_matching_dom;
    if matching_domain%found then
      -- If there is a domain with the same name as the attribute,
      if cur_matching_dom.dom_format = att.v.format and
         nvl(cur_matching_dom.dom_avg_att_len,0) = nvl(att.v.average_length,0) and
         nvl(cur_matching_dom.dom_max_att_len,0) = nvl(att.v.maximum_length,0) and
         nvl(cur_matching_dom.dom_att_prec,0) = nvl(att.v.precision,0) then
         -- If the datatype match is perfect, no new domain is
         -- required.
         domain_to_create := null;
         matching_dom_id := cur_matching_dom.dom_id;
         -- If the datatype match is not perfect, we'll create a new
         -- domain.
         domain_to_create := cur_no_dom_attribute.att_name||'_'||
      end if;
      -- If there is no domain with the same name as
      -- the attribute, we'll create a new domain.
      domain_to_create :=;
    end if;
    close matching_domain;

    if domain_to_create is not null then
      dom.v.application_system_owned_by := v_app_id;
      dom.i.application_system_owned_by := TRUE; := substr(domain_to_create,1,40); := TRUE;
      dom.v.format := att.v.format;
      dom.i.format := TRUE;
      dom.v.maximum_attribute_length := att.v.maximum_length;
      dom.i.maximum_attribute_length := TRUE;
      dom.v.average_attribute_length := att.v.average_length;
      dom.i.average_attribute_length := TRUE;
      dom.v.attribute_precision := att.v.precision;
      dom.i.attribute_precision := TRUE;
      dom.v.description := att.v.notes;
      dom.i.description := TRUE;
      dom.v.datatype := att.v.format;
      dom.i.datatype := TRUE;
      dom.v.maximum_column_length := att.v.maximum_length;
      dom.i.maximum_column_length := TRUE;
      dom.v.average_column_length := att.v.average_length;
      dom.i.average_column_length := TRUE;
      dom.v.column_precision := att.v.precision;
      dom.i.column_precision := TRUE;
      matching_dom_id :=;
    end if;

    -- assign the attribute to the domain
    att.v.domain_reference := matching_dom_id;
    att.i.domain_reference := TRUE;
    -- Update the attribute
    cioattribute.upd(cur_no_dom_attribute.att_id, att);

    -- Validate the update

    -- Get feedback


    -- If the activity did not close successfully, roll back
    -- all changes made during the activity
    if act_status != 'Y' then
      dbms_output.put_line('Activity aborted with constraint violations');
    end if;
  end loop;
  when unknown_application_system then
    dbms_output.put_line('Sorry, the application that you have entered is unknown.');
  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
      end loop;
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with API errors');
        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
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with ORACLE internal errors');
        dbms_output.put_line('API Session aborted with ORACLE internal errors');
      end if;
    end if;

Please, do not expect too much from this utility. It merely provides a convenient first-cut. There is usually still a fair amount of manual work to get it right. The typical manual task to take care of is to merge 2 or more domains into one. In order to be one the safe side, this utility does not change the data definition of two attributes with the same name. Let us take a simple example. If we have attribute zip5 (char(5)) in entity customer and attribute zip5 (varchar2(5)) in entity order, this utility will:

In other words, if a domain with the same name as the current is found, but its characteristics (format, max length, avg length, precision) are different, the utility creates a new domain with name '<attribute name>_<entity_name>'.
Most of the time you will decide to merge domains zip5 and zip5_order.

Multi Application Considerations

You may be working on a corporate system. If not corporate, your system may be comprised of several applications. Even if your system is currently defined as one application, it will grow. You may soon decide to divide it in several more manageable pieces. If this is the case the ideas that follow are worth considering.

It is usually a good idea to avoid domain dulication across application systems. Domains like zip, short_name, short_comment, person_name, city_name will be required in several application systems. The best strategy is to define them in one application and share them to the others. The question is: what application should own our domains. There are three options:

  1. domains may be owned by the application that uses then most;
  2. all domains must be owned by one "reference" application;
  3. compromise between the 2 first options, i.e. very common domains like zip are to be owned by the "reference" application.
Option 1 is not very practical. Which application should own domains zip3, zip5, zip9, short_description, etc.

Option 2 can put you in a situation where you may end up with a number of domains that is difficult to manage. But the concept is very simple and the team will always know where to go to find the domain master.

Option 3 may be the best one, depending on the size of your repository and where you draw the line between reference and non reference. But it complicates things for the team and the repository administrator.

All in all, in most circumpstances, we recommend Option 2.

Please send me comments or suggestions:

 Take a look at my HomePage