Designer 2000 API

Jump Start a new application in Designer 2000

By Hervé Deschamps.

There are so many things that we do again and again on every new application that we create in Designer 2000.

One of them is to create common  domains like zip, person_name, street_address, city, state, phone_no, weight, volume, email_address, etc. If you happen to work in the same repository for long enough, you may re-use the work from one application system to another. If you are a consultant (like me) who travel from client site to client site, this article will help you out.

Here is the idea:

Is this not simple?

I will provide all the scripts you need:
Create the jump_domains table
Populate the jump_domain table
Copy the domains in Designer 2000

create table jump_domains (
name                        varchar2(40) not null,
format                      varchar2(10),
max_att_length              number(5,0),
avg_att_length              number(5,0),
att_precision               number(2,0),
datatype                    varchar2(16),
max_col_length              number(5,0),
avg_col_length              number(5,0),
col_precision               number(2,0),
description                 varchar2(240)
insert into jump_domains values(
'Free text zone used for description purposes.')
insert into jump_domains values(
'Free text zone used for description purposes.')
insert into jump_domains values(
'3 digit ZIP code.')
insert into jump_domains values(
'5 digit ZIP code.')
insert into jump_domains values(
'9 digit ZIP code.')
insert into jump_domains values(
'Name of a city.')
insert into jump_domains values(
'Name of a state.')
insert into jump_domains values(
'Free text zone used for description purposes.')

I have a table export with many more useful domains. It grows as time goes on. Send me an email and if send me lots of money I'll send you my latest export.

set serveroutput on
set verify off
rem ******************************************************
rem * 
rem * This utility create common domains in a new
rem * application in Designer 2000.
rem *
rem * It basically copies the content of table jump_domains
rem * into Designer 2000 repository.
rem *
rem * Written by: H. Deschamps, 7/8/97.
rem * Last Modified: 7/8/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 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
     from ci_domains dom
     where = p_name and
           dom.application_system_owned_by = p_app_id;

  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_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
      dom.v.application_system_owned_by := v_app_id;
      dom.i.application_system_owned_by := TRUE; := cur_jump_domain.dom_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;
      -- 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 if;
    close matching_domain;
  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;

If some of the domains in table jump_domain are already in your application, the utility will not do anything with them:

The utility will just go to the next domain in table jump_domain.

Next enhancement: create allowed values for domains like YESNO.

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