Top Ten API Tips

Dr. Paul Dorsey and Joseph Strano

Dulcian Inc.

 

Oracle’s Designer/2000 is an extremely powerful and helpful tool; but some aspects of its user interface can be very annoying.  For example, wouldn’t it be nice to delete a domain without having to de-assign it from every attribute first?  Fortunately, most of the shortcomings of Designer/2000 can be compensated for by using the API functions included in the Designer/2000 package.  This article will list ten useful tips about these functions. The reader needs no previous knowledge of the API functions to understand and use these tips.

 

I.  Code needed to begin and end API session

Each time you want to use the API functions, you need to begin and end the procedure in the same fashion.  The following table shows the code needed to do this along with a description of what each command does.

Code

Comments

cdapi.initialize(‘APP_NAME’);

Replace APP_NAME with the name of the application you want to modify.

cdapi.open_activity;

Makes the API functions accessible.

<CODE>

All of your code

cdapi.close_activity(act_status)

ACT_STATUS is a varchar2 variable that you need to declare.  When the system attempts to close the activity, it will return a ‘Y’ if it closed successfully, ‘N’ if it had constraint violations.

 

II.  API Functions

There are three API functions that can be used often.  They are:

UPD, INS, and DEL.  Each one will be explained with examples given for the ENTITIES view. 

UPD

 

The syntax for this command is:  CIOEntity.UPD(ID,DATA).

This command updates the record that has the ID that is passed. DATA is a variable declared as type CIOENTITIES.DATA.  This type of variable will be described shortly.

 

INS

 

The syntax is:  CIOEntity.UPD (#,DATA).  # means any number variable.

This command inserts a new item to the repository. Oracle automatically generates the ID for the new item, so I still do not know what Oracle needs the number variable for.  As far as I can tell, the function doesn’t use it.

 

DEL

The syntax is:  CIOENTITY.DEL(ID).

This command deletes the item with the ID that is passed.

 

 

 

The DATA variable needs to be declared before it can be used.  It is a record variable that has the same basic structure as the view to which corresponds except that it only has the columns the user can change via the API tools.  The user can make changes to a record by setting this variable.  Here is an example:

 

Declare

  R_Ent CIOEntities.Data;

Begin

/* Assume all initialize code is here */

/* 1 */ R_Ent.v.name := ‘Entity Name’;

/* 2 */ R_Ent.i.name := TRUE;

 

Line 1 sets the value of the name field to Entity Name. 

Line 2 tells the system that the name field has been changed. 

When the UPD or INS is fired, the system only changes those fields where the I parameter has been set to TRUE.  No matter what repository view you are manipulating, this syntax structure remains the same.

 

III.  Entities and Attributes

Entity and Attribute information is stored in the CI_ENTITIES and CI_ATTIRBUTES repository views.  The entity and attribute descriptions can be found in the SDD_DESCRIPTIONS repository table.  In order to manipulate information in Entity and Attribute view, you need to declare two variables: 

1.      type CIOENTITIES.DATA

2.      type CIOATTRIBUTES.DATA.

 

An API script can be written to perform batch changes to the repository.  If you want to create your entire logical model with one command, create a formatted text file with the information for your entities and attributes in it.  Then, create a script that breaks this text file down and inserts the information into the repository, one item at a time.

 

IV.  Domains

Domain information is stored in the CI_DOMAINS repository view.  The domain descriptions can be found in the SDD_DESCRIPTIONS table.  In order to manipulate information in the Domain view, you need to declare a variable of type CIODOMAINS.DATA.

 

Domains can be extremely helpful in maintaining standards within a model.  They can even go so far as to maintain standards within all applications written by an organization.  Unfortunately, domains are not used 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.

 

One of the biggest problems with domains, is that in order to delete one, you need to ensure that no attribute or column is using that domain.  That means going to each attribute or column that has that domain assigned, and assigning a different or null domain.  A script can be written to automatically NULL out the domain reference of each attribute or column that has that domain assigned to it, then delete the domain from the repository.  The script that would handle this is detailed in section IX:API_Delete.

 

V.  Tables and Columns

Table and Column information can be found in the CI_TABLE_DEFINITIONS and CI_COLUMNS repository views.  In order to manipulate them, you need to declare variables of type: CIOTABLE_DEFINITIONS.DATA and CIOCOLUMNS.DATA.

 

There are a number of functions that can be performed more quickly by writing an API script. The one that is the most useful is that of creating new tables and columns.  By creating an insert API script, the developer can enter the entire physical database into a text file.  Then, once the API script is run, the rest of the work gets done by the system.

 

VI.  Abbreviation Implementation

When a logical model is built, the most descriptive names possible should be used for items.  But, when moving the model to the physical level, names must not only be descriptive, but also easy for the 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.

 

First, create a table with the full word and the approved abbreviation for that word.  Then, a function can be written to look for key words either with an underscore after them, or at the end of the string.  The function can replace the words found with the approved abbreviation.  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.

 

VII.  Automatic Constraint Namer

How many times have you looked at a constraint and not known what it was used for?  That problem can be solved by writing a script that names your constraints using set standards.  For instance, for a foreign key, the name could be <MASTER_TABLE>_<DETAIL_TABLE>_FK.  This script could be written so it looks at every constraint in your repository, and automatically re-names it to fit your standards.

 

VIII. Spreadsheet Interface

If you want to give your developers a tool to enter bulk information into the repository, but don’t want to take the time to write a Developer/2000 application, it is possible to build scripts that work off of output from a spreadsheet.  Have the developer save the spreadsheet as a comma delimited text file.  Your script can then read this text file and make the appropriate changes to the repository.

 

IX.  Developer/2000 Interface

An application can be developed using Developer/2000 to update, delete, and create items (For the sake of brevity, this paper will only cover domains). The repository information is copied over into a local table.  The application then allows the user to manipulate the information in this local table.  Once the user is finished, the application writes all of the changes the user made back to the repository at the same time.  This saves a great deal of time.  Now, instead of saving after each change, the user can make all of the necessary changes in one session, press the save button, and go get a cup of coffee.  When they return, their repository will be fully updated.

 

Local Table

The following is the table that needs to be built for this application:

 

Domain

Code

Comments

domain_uid number(38)

System Assigned (Uses Sequence #)

domain_oid number(38)

Oracle Assigned ID (ID)

domain_name varchar2(100)

NAME

maximum_length number(23,3)

Maximum Attribute Length

format varchar2(70)

Format

precision varchar2(4),

Precision

comments varchar2(700)

SDD_DESCRIPTIONS

app_id number(38)

APPLICATION_SYSTEM_OWNED_BY

status varchar2(20)

Locally Assigned UNCHANGED, CHANGED, NEW

delete_flag char(1)

Locally Assigned Y, N

Primary Key(Domain_UID)

 

Unique(Domain_Name,App_ID)

 

 

Oracle will not generate an ID for items until they are written to the repository, so you can’t use the Oracle ID to link these three tables together. The _UID was created and is populated by a sequence that you build.

 

Database Programs

Since Developer/2000 will not recognize the API functions, all PL/SQL programs that you write using these functions need to be placed in the database.  Below are the descriptions and code for the three PL/SQL packages that are needed for this application.

 

API_UPDATE_DOMAIN

Called when the user makes changes to existing items. The changes will be saved to the existing items in the repository.

 

Code

Comments

Procedure Domain(in_ID Number,in_Name Varchar2,in_Length Number,in_Format Varchar2,in_Precision Varchar2,in_app_name varchar2) IS

in_ID - The Oracle ID of the domain to change

in_Name - in_precision  The information the user updated

In_app_name - The name of the application that owns the domain to be changed.

R_Dom CIODomain.Data;

act_status varchar2(255);

R_Ent - Record variable with the structure of CI_DOMAINS.

Act_Status - Gets the status of the Close_Activity operation.

cdapi.initialize(in_app_name);

  cdapi.open_activity;

Initialize the API functions

r_dom.v.name := in_name;

  r_dom.i.name := TRUE;

  r_dom.v.maximum_attribute_length := in_length;

  r_dom.i.maximum_attribute_length := TRUE;

  r_dom.v.format := in_format;

  r_dom.i.format := TRUE;

  r_dom.v.attribute_precision := in_precision;

  r_dom.i.attribute_precision := TRUE;

Update all of the information

ciodomain.UPD(in_id,r_dom);

Update the domain.

cdapi.close_activity(act_status);

Close the API functions.

 

Add_Comments

This procedure will delete the old description, and insert the new description.  This was easier than updating the existing comment because Oracle breaks the description down into 70 characters per record.

Code

Comments

Procedure Add_Comments(in_id number,in_comments varchar2) IS

in_id - The ID of the item the description belongs to.

In_comments - The new description.

comment_left varchar2(700);

pos_space number(3) := 0;

char_left number(3);

start_pos number(3);

counter number(3) := 1;

len number;

comment_strip varchar2(70);

Comment_Left - After we strip off 70 characters, this is what’s left of the description.

Pos_Space - Position of the last space

Char_Left - The number of character left in the string

Start_Pos - Starting Position

Counter - Used as an order number when writing to repository.

Len - Length of the string

Comment Strip - Current piece of the description.

delete from sdd_descriptions where dsc_ref = in_id;

Delete the description from the repository.

comment_left := in_comments;

Copy the new description to a local variable.

while comment_left is not null loop

Repeat the loop as long as there is still some description that needs to be saved.

char_left := length(comment_left);

    if char_left > 70 then

Determine if there are more than 70 characters left.

start_pos := (char_left-69) * -1;

      pos_space := instr(comment_left,' ',start_pos,1);

Calculate the position of the 1st space before the 70th character.

if pos_space = 0 then

        pos_space := 70;

      end if;

If it doesn’t find a space, set the position to 70.

comment_strip := substr(comment_left,1,pos_space);

Get the 70 character piece of the description.  We ensured the break character was a space (word wrap) to make it easy to piece back together.

Else

      comment_strip := comment_left;

      comment_left := null;

    end if;

If there are only 70 characters left, then copy the whole string out of comment_left and make it null so our while loop will end.

insert into sdd_descriptions values(in_id,counter,'CDIDSC',comment_strip);

Write the current piece of the description to the repository.

counter := counter + 1;

    comment_left := substr(comment_left,pos_space + 1);

Update the counter, and remove the piece of the description that we just stored from our local variable.

end loop;

  commit;

END;

END;

Commit the changes and end the procedure.

 

API_INSERT

The logic behind these procedures is exactly the same as the Update procedures with the exception that we call the INS function instead of the UPD function.  There is no procedure in this package for descriptions since descriptions are handled by the ADD_COMMENTS procedure in the API_UPDATE package.

 

API_DELETE

The delete procedure is relatively straightforward, but we have to un-assign the domain before we can delete it.

 

procedure domain(in_id number,in_app_name varchar2) IS

 

cursor c1 is

  select ID

  from ci_attributes

  where domain_reference = in_id;

R_Att cioattribute.data;

act_status varchar2(255);

 

BEGIN

  cdapi.initialize(in_app_name);

  cdapi.open_activity;

/* Un-assign the domain */

  for c1ind in c1 loop

    R_Att.v.Domain_Reference := NULL;

    R_Att.i.Domain_Reference := TRUE;

    cioattribute.UPD(c1ind.ID,R_Att);

  end loop;

/* Delete the Domain */

  ciodomain.DEL(in_id);

  cdapi.close_activity(act_status);

END;

 

You would also have to do this if the domain is assigned to columns, sub-domains or application systems (other than the context one).

 

The delete comments procedure is two lines of code where in_id is a parameter that holds the ID of the object that owns the description you want to delete:

 

delete from sdd_descriptions where dsc_ref = in_id;

  commit;

 

Library

We stored the routines to copy the information from the repository to our local tables, and the routines that write the changes made in our local tables back to the repository in a library.  This library was then attached to the form.  Below is the commented code for these procedures:

 

PROCEDURE copy_domains(App_ID Number) IS

 

/* Copies all attributes and their descriptions associated

|| with the entity who’s ID gets passed in Ent_OID.

|| Dulcian Inc.

*/

 

current_id number; -- Current Domain OID

domain_genid number; -- Next available Domain UID

Whole_Desc Varchar2(2000); -- Domain description put together

 

/* Gets my next available Domain UID */

cursor c1 is

  select seq_domain.nextval

  from dual;

 

/* Get all relevant information for domains associated with the

|| Current application

*/

cursor c2 is

  select id,name,maximum_attribute_length,format,attribute_precision

  from ci_domains

  where application_system_owned_by = App_ID;

 

/* Get the entire description for the current attribute */

Cursor c3 is

  Select dsc_description

  From sdd_descriptions

  where dsc_type = 'CDIDSC'

  and dsc_ref = Current_ID;

 

BEGIN

 

  /* Walk the Domain Table */

  For C2Ind In C2 Loop

    Current_ID := C2Ind.ID;

 

    /* Piece together the domain's description */

    For C3Ind In C3 Loop

      Whole_Desc := Whole_Desc || C3Ind.Dsc_Description;

    End Loop;

 

    /* Fetch the next available UID */

    Open c1;

    fetch c1 into domain_genid;

    close c1;

    Insert Into Domain Values(domain_genid,c2ind.id,c2ind.name,c2ind.maximum_attribute_length,c2ind.format,c2ind.attribute_precision,Whole_Desc,App_ID,'UNCHANGED','N');

  End Loop;

  Forms_DDL('COMMIT');

END;

 

PROCEDURE Write_To(In_App_ID Number) IS

 

/*

|| Writes all changes to the repository.

|| Dulcian Inc.

*/

 

  cur_app_id number(38);

  cur_domain_id number(38);

  ora_domain_id number(38);

  cur_app_name varchar2(100);

  cur_dom_name varchar2(100);

  new_domain_id number(38);

  Delete_Entity Char(1) := 'N';

  Delete_Att Char(1) := 'N';

 

 

/* Find the Oracle ID of the current domain */

  Cursor c5 is

    select domain_oid

    from domain

    where domain_uid = cur_domain_id;

 

  /* Get the name of the current application */

  Cursor c6 is

    select name

    from ci_application_systems

    where id = Cur_App_ID;

 

  /* Select all domains */

  Cursor c7 is

    select *

    from domain;

 

  /* Find the Oracle ID for the newly created domain */

  cursor c8 is

    SELECT id

    FROM ci_domains

    WHERE name = UPPER(cur_dom_name)

    AND application_system_owned_by = cur_app_id;

 

  Null_App Exception;

BEGIN

 

/* General Setup */

If In_App_ID Is Null Then

  Raise Null_App;

End If;

Cur_App_ID := In_App_ID;

 

/* Get application name */

open c6;

fetch c6 into cur_app_name;

close c6;

 

/* Walk the domain table */

For C7Ind in c7 loop

  cur_domain_id := c7ind.domain_uid;

 

  /* Delete the domain? */

  If C7Ind.Delete_Flag = 'Y' Then

    API_Delete.Domain(c7ind.domain_oid,cur_app_name);

    API_Delete.Comments(c7ind.domain_oid);

 

  /* Insert the domain? */

  ElsIf C7Ind.Status = 'NEW' Then

    API_Insert.Domain(c7ind.domain_name,c7ind.maximum_length,c7ind.format,c7ind.precision,cur_app_name);

 

    /* Get the newly generated Domain OID and store it in our Domain Table */

    cur_dom_name := c7ind.domain_name;

    open c8;

    fetch c8 into new_domain_id;

    close c8;

    Update Domain Set Domain_OID = new_domain_id,Status = 'UNCHANGED' where Domain_UID = Cur_Domain_ID;

 

    /* Store the comment */

    API_Update.Add_Comments(New_Domain_ID,c7ind.comments);

    Forms_DDL('Commit');

 

  /* Update the domain */

  ElsIf C7Ind.Status = 'UPDATE' Then

    API_Update.Domain(c7ind.domain_oid,c7ind.domain_name,c7ind.maximum_length,c7ind.format,c7ind.precision,cur_app_name);

    API_Update.Add_Comments(C7Ind.Domain_OID,C7Ind.Comments);

    Update Domain Set Status = 'UNCHANGED' Where Domain_UID = Cur_Domain_ID;

  End If;

End Loop;

END;

 

Front End

The user first selects “File|Copy From” from the main menu to copy the domain information to the local table.  This form is linked to the local Domain table, so all changes are done locally.  When the user is finished, clicking on “File|Write To” transfers all of the changes back to the repository.

 

X.  Developer/2000 Reports

Wouldn’t it be nice to do a describe on an entity that exists in the repository?  It is possible if you write a 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

 

 

Here is the data model:

 

Conclusion

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 you will save while developing.