Dr. Paul Dorsey and Joseph Strano

Dulcian Inc.

Designer/2000 is an extremely powerful and helpful tool, with an easy to use interface. It is possible to make this product even more powerful by designing customized applications using the Application Programmatic Interface (API) functions that are included in the Designer/2000 package. The API gives the developer the ability to make direct manipulations of the repository database without using the Designer/2000 product itself.

This article will list ten useful tips about these functions. The reader needs basic knowledge of the API functions to understand these tips. Explanations and examples of what can be done with the API functions are listed in two sections:

• Section I describes ten useful tips that can be written to make effective use of the API.
• Section II includes several code extracts from some API applications that we have built.

The following ten tips demonstrate ways in which the API can be used to enhance the power and performance of Designer/2000.

Using Oracle Forms 4.5, an application can be built to create an entire logical model without touching the repository. In this architecture, information is extracted from the repository tables. Then, the developer, using a Forms front-end, manipulates the data in those tables. Finally, when all of the changes are made, a batch update is done to the repository.

This type of system allows the manipulation of the database by personnel who do not have access to the repository. Information can be copied from the repository to local tables that you build. A front-end can then be built that connects to these tables, allowing the user to manipulate this local information. Once all of the changes are made, a user with access to Designer/2000 can fire off a function that updates the repository with the information in the local tables.

Here is an example of a table that could be built to hold entity information:

A screen shot of the application we designed to edit entities and attributes is shown in Figure 1:

The user can edit any information in the application by choosing the application system from the application drop down list, as long as the information has been copied from the repository. We were also able to force users to define attributes using domains. The benefits of this will be explained in the next section.

Domains can be extremely helpful in keeping standards consistent within a model. They can even go so far as to enforce consistency within all of the applications written by an organization. Unfortunately, they are not used often enough. There have been many times when we have come across a database that had name fields of five different sizes. If the use of domains were made easier, maybe more developers would utilize them in their applications.

A Forms 4.5 application can be built to manipulate domain information, just as with entities and attributes. Figure 2 shows a screen shot of the application we built to handle domain administration:

This allows personnel who do not have access to the repository to enter the information, freeing up system personnel for more important tasks. Once audited, the information can be copied back by firing a function.

By using this application, along with the Entities and Attributes application explained in the previous section, all of the tools necessary to create a logical data model can be accessed without Designer/2000. Designer/2000 doesn’t even need to be on, or even accessible by, the computer where work is being done. Once the information is copied into the local tables, the data can first be exported, and then imported to any machine that has access to an Oracle database.

Using the above two applications, a novice developer can be given the task of entering Designer/2000 information without needing constant supervision. Any errors they make are isolated in the local tables. If the errors are minor, they can be corrected locally with little effort. If the errors are major, the tables can be erased, and the old information can be copied from the repository, essentially performing a rollback of all the changes. Once the work has been checked, and no errors are found, the system will write all of the changes to the repository automatically.

When a logical model is built, the most descriptive names possible should be used for items. But, when promoting the model to the physical level, names must not only be descriptive, but also easy for developers to use. Unfortunately, much of the time, obscure or confusing table names are selected. To solve this problem, it is easy to create an application that automatically abbreviates words in a useful and consistent way, thus enforcing standardized abbreviations.

To accomplish this, first, create a lookup table that has two columns: the full word, and the approved abbreviation for that word. Then, a function can be written to look for key words, and replace them with the approved abbreviation (e.g. replace "employee" with "EMP"). Although populating the lookup table is a work-intensive task, once accomplished, the amount of time and aggravation saved should make the time spent on data entry well worth the effort.

Writing a Forms application will allow users to create the entire physical model without touching the repository. The reasoning for doing this is the same as that stated in the Entities/Attributes section.

If columns are added at the physical level using abbreviations, how can we reverse engineer them to the logical model with their full names? If the columns were added using the Forms application, an API script can be written that calls the Abbreviation function described in the previous section. This function looks for the abbreviation, and, if found, replaces it with the full word, before writing to the repository.

How many times have you looked at a constraint and not known what it was used for? Writing a script that names your constraints using set standards can solve that problem. For example, for a foreign key, the name might be <MASTER_TABLE>_<DETAIL_TABLE>_FK. This script can be written to find every constraint in the repository, and automatically re-names it to fit your specific standards.

By building scripts that would read from and write to delimited text files, it is possible to manipulate the repository without having access to any Oracle products. To do this, write an API routine that copies the information you want to a formatted text file. The user can then open this file using any spreadsheet application, make whatever changes necessary, and save back to a formatted text file. Once this file is audited, a script is fired updating the repository with the information that was in the text file.

There are a few obvious reports missing from the Designer/2000 product. Perhaps the most serious is a simple report that shows entities and attributes with their descriptions. You can create this report using Oracle Reports 2.5. Here is the query behind the describe report:

select Tables.NAME TName,

Tables.ID TID,

format DATATYPE,

MAXIMUM_LENGTH,

Columns.NAME CName,

optional_flag NULL_INDICATOR,

Columns.ID CID,

domain_reference,

precision decimal_places

from CI_Entities Tables,

CI_Attributes Columns,

CI_APPLICATION_SYSTEMS Owners

where Tables.ID = Columns.Entity_Reference

and Tables.application_system_owned_by = Owners.id

and Owners.name =: SYSTEM_OWNER

The data model is shown in Figure 3:

The descriptions are handled through two formula columns:

• TDesc – Shows the description for the entity
• CDesc – Shows the description for the attribute

Here is the code behind the TDESC formula column:

function TDescFormula return Char is

cursor c1 is

select dsc_description

from sdd_descriptions

where dsc_ref = :tid;

outchar varchar2(1000);

begin

for c1ind in c1 loop

outchar := outchar|| c1ind.dsc_description || ' ';

end loop;

return outchar;

exception when others then

return null;

end;

All descriptions are held in the SDD_DESCRIPTIONS table. Each record holds 70 characters of the description. This formula retrieves all of the descriptions for the current entity and pieces them together.

Using a structure similar to that used for describing entities, a report can be designed to describe tables and their associated columns that reside in the repository for a particular application. This report has the added feature of showing the description for the table and columns.

Here is the query behind the report:

Select Tables.NAME TName,

Tables.ID TID,

DATATYPE,

MAXIMUM_LENGTH,

Columns.NAME CName,

NULL_INDICATOR,

Columns.ID CID,

domain_reference,

decimal_places

from CI_TABLE_DEFINITIONS Tables,

CI_COLUMNS Columns,

CI_APPLICATION_SYSTEMS Owners

where Tables.ID = Columns.Table_Reference

and Tables.application_system_owned_by = Owners.id

and Owners.name =: SYSTEM_OWNER

The data model is the same as the entity report and will not be shown.

A report can be written to show all tables and their associated constraints that exist in the repository for a particular application. The data model for this report is shown in Figure 4:

Here is the query behind the Tables_And_Columns model:

select Tables.NAME TName,

Tables.ID TID,

DATATYPE,

MAXIMUM_LENGTH,

Columns.NAME CName,

NULL_INDICATOR,

Columns.ID CID,

domain_reference,

decimal_places

from CI_TABLE_DEFINITIONS Tables,

CI_COLUMNS Columns,

CI_APPLICATION_SYSTEMS Owners

where Tables.ID = Columns.Table_Reference

and Tables.application_system_owned_by = Owners.id

and Owners.name =:SYSTEM_OWNER

order by Cname

Here is the query behind the Constraints model:

select COLUMN_REFERENCE,

CONSTRAINT_NAME,

foreign_column_reference,

CONSTRAINT_TYPE

from CI_KEY_COMPONENTS

The following are examples of procedures needed to build the structures described in the previous section.

Update_Domain

This function will update one domain with the information that is passed to it. This function will update the following fields of the CI_DOMAINS view: name, maximum_attribute_length, format, and attribute_precision.

This procedure will update the description of an item. If a description already exists for the item, this function will delete the old one and insert the new one. Note that this routine directly manipulates the SDD_DESCRIPTIONS table and does not use the API. There is no API command to manipulate descriptions. Therefore, this routine is neither recommended nor endorsed by Oracle. However, it is the only choice if you want to manipulate item descriptions.

The repository stores descriptions in 70 character chunks. This function uses word wrap logic to ensure that we break on a space. This makes it easier to piece the description back together and doesn’t adversely affect Designer/2000.

This procedure copies all domains and their descriptions for the selected application to a local table called DOMAIN.

Once you get started writing API scripts, you will find hundreds of ways to use them to make your life easier. The time you invest in writing these scripts will be well spent considering the time saved while developing.