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:
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(
'DESCRIPTION_LONG','VARCHAR2',2000,100,NULL, 'VARCHAR2',2000,100,NULL, 'Free text zone used for description purposes.') / insert into jump_domains values( 'DESCRIPTION_SHORT','VARCHAR2',200,40,NULL, 'VARCHAR2',200,40,NULL, 'Free text zone used for description purposes.') / insert into jump_domains values( 'ZIP3','VARCHAR2',3,3,NULL, 'VARCHAR2',3,3,NULL, '3 digit ZIP code.') / insert into jump_domains values( 'ZIP5','VARCHAR2',5,5,NULL, 'VARCHAR2',5,5,NULL, '5 digit ZIP code.') / insert into jump_domains values( 'ZIP9','VARCHAR2',9,5,NULL, 'VARCHAR2',9,5,NULL, '9 digit ZIP code.') / insert into jump_domains values( 'CITY','VARCHAR2',50,20,NULL, 'VARCHAR2',50,20,NULL, 'Name of a city.') / insert into jump_domains values( 'STATE','VARCHAR2',50,20,NULL, 'VARCHAR2',50,20,NULL, 'Name of a state.') / insert into jump_domains values( 'WEIGHT','NUMBER',9,4,2, 'NUMBER',9,4,2, '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 ****************************************************** declare
v_app_id number(38);
cursor jmp_domains is
cursor matching_domain (p_name varchar2,
procedure instantiate_messages is
begin -- Get Application ID
-- Initialize API if not already done
-- Set DBMS Output Buffer to Max Size
for cur_jump_domain in jmp_domains loop
-- If there is no such domain already, create
one.
-- Otherwise, this must have been an ORACLE SQL
or internal error so
|
If some of the domains in table jump_domain are already in your application, the utility will not do anything with them:
Next enhancement: create allowed values for domains like YESNO.
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:
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: herve@iherve.com