Oracle Designer 2.1.2. API


Enforcing Column Naming Standards (New Edition)

By Hervé Deschamps.



The bigger the project, the more important are naming standards. In this article we focus on column naming standards. We present a simple technique and a utility to enforce it. Of course, this technique can be extended to tables, foreign key, indexes, modules, entities, attributes, etc. The utility in this article was tested by the author on a number of versions of Oracle's CASE tool, from Designer /2000 1.3.2 to Oracle Designer 2.1.2.

Part of the task of enforcing naming standards can be reduced to a simple search and replace function, just as in a word processor. For example, we may look for all instances of the character string 'MODIFIED' and replace them with 'MDFD'. Or replace 'IDENTIFICATION' with 'ID', 'PURCHASE' with 'PURCH', 'CUSTOMER' with 'CUST'. A column named CUSTOMER_IDENTIFICATION would then become CUST_ID. Unfortunately there is no search/replace utility in Oracle Designer 2.1.2. This task can be done manually at the risk of inconsistencies and typing mistakes. In practice it is seldom performed because it is extremely tedious thus volunteers are scarce, because it requires maintenance when the data model changes and because it is too time consuming for tight project timescales. We will show how we can put a script together that will do this for us. This script issues a few calls to the Oracle Designer API. It can be run several times throughout the duration of the project.

This technique is not all there is to enforcing standards in Oracle Designer, but it can save a considerable time. Please feel to paste the code in SQL*Plus as you go along.

First, we need a simple table with 2 columns and one primary key constraint:
 

create table case_abbrev 
( full_word varchar2(50), 
  abbrev_word varchar2(50)) 
/

alter table case_abbrev
 add constraint cabb_pk primary key 
  (full_word)
 using index 
/

 

It is on purpose that we did not create a primary key or unique key on the abbreviation. This is because several long words like IDENTIFIER and IDENTIFICATION often require to be abbreviated in the same way: ID.

Here are some typical values that we can insert in this table:
 

full_word abbrev_word
CUSTOMER CUST
ORDER ORD
MODIFIED MDFD
CREATED CRTD
PURCHASE PURCH
DRAWING DRW
IDENTIFICATION ID
NUMBER NO
INDICATOR IND
insert into case_abbrev values ('CUSTOMER','CUST')
/
insert into case_abbrev values ('ORDER','ORD')
/
insert into case_abbrev values ('MODIFIED','MDFD')
/
insert into case_abbrev values ('CREATED','CRTD')
/
insert into case_abbrev values ('PURCHASE','PURCH')
/
insert into case_abbrev values ('DRAWING','DRW')
/
insert into case_abbrev values ('IDENTIFICATION','ID')
/
insert into case_abbrev values ('NUMBER','NBR')
/
insert into case_abbrev values ('INDICATOR','IND')
/
Click here to download the Oracle 8 export file of my latest abbreviation table (50+ abbreviations).

Once we have this, we can already do a bit of impact analysis: "Dear Sql*Plus, show me all the search/replace that my utility would do if I run it".
 
 

prompt
prompt

rem    ******************************************************
rem    * 
prompt * This utility reports the columns that violate the
prompt * naming conventions in Designer 2000.
prompt * It uses table case_abbrev that contains all the words
prompt * that must be abbreviated, and the standard abbreviation.
rem    *
rem    * Written by: H. Deschamps, 6/23/97.
rem    * Last Modified: 12/21/98.
rem    ******************************************************
prompt
prompt
prompt

set arraysize 1
set pagesize 1000
set linesize 150
col table_name format a30
col column_name format a30
col incorrect_word format a20
col correct_to format a10

spool guilty_col
break on table_name on column_name

  -- Report on "Guilty" Columns
  select tab.name table_name, 
         col.name column_name, 
         abv.full_word incorrect_word, 
         abv.abbrev_word correct_to
  from  ci_columns col, 
        ci_application_systems app,
        case_abbrev abv, 
        ci_table_definitions tab
  where col.table_reference = tab.id and
        tab.application_system_owned_by = app.id and
        app.name = upper('&&app_name') and
        app.version = &&app_version and
        col.name like '%'||full_word||'%'
  order by tab.name, col.name, abv.full_word
/

spool out

Now that the scene is set, you can just run the script in the next table. But before you do, please bear in mind that changing column names may have an impact on: It is a good idea to enforce standards from the beginning of design and in a continuous way.
Waking up at the end of design or build will be very costly to the project.
 
 
set serveroutput on 
set verify off
rem ****************************************************** 
rem * 
rem * This utility enforces column naming conventions using 
rem * Designer 2000. 
rem * It uses table case_abbrev that contains all the words 
rem * that must be abbreviated, and the standard abbreviation. 
rem * It also uses function abbrv_name to reformat the 
rem * "guilty" column. 
rem * 
rem * Written by: H. Deschamps, 6/23/97. 
rem * Last Modified: 12/21/98. 
rem ****************************************************** 

declare 
  col ciocolumn.data; 
  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 guilty_columns (p_app_id number) is 
    select tab.name tab_name, 
           col.id col_id, 
           col.name col_name, 
           full_word full_word, 
           abbrev_word abbrev_word 
    from  ci_columns col, 
          case_abbrev, 
          ci_table_definitions tab 
    where col.table_reference = tab.id and 
          tab.application_system_owned_by = p_app_id and 
          col.name like '%'||full_word||'%'; 
  cur_guilty_column guilty_columns%rowtype; 
 

  function abbrv_name( 
      p_original_name in varchar2, 
      p_key_word in varchar2, 
      p_abbrev_word in varchar2) 
     return varchar2 is 
  begin 
     return (replace(p_original_name,p_key_word,p_abbrev_word)); 
  end; 

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

begin 

  -- 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 
    raise_application_error(-20103, 
         '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 
  dbms_output.enable(1000000); 

  for cur_guilty_column in guilty_columns(v_app_id) loop 
    cdapi.open_activity; 
 
    -- Get the guilty column's record. 
    ciocolumn.sel(cur_guilty_column.col_id ,col); 

    -- Correct the column name. 
    -- Uncomment 2 lines below for debugging.
    -- dbms_output.put_line(col.v.name ||' <- ('||cur_guilty_column.full_word||
    --                                      ','||cur_guilty_column.abbrev_word||').');
    col.v.name := abbrv_name(col.v.name, 
                             cur_guilty_column.full_word, 
                             cur_guilty_column.abbrev_word); 
    col.i.name := TRUE; 
 
    -- Update the column 
    ciocolumn.upd(cur_guilty_column.col_id, col); 

    -- Validate the update 
    cdapi.validate_activity(act_status,act_warnings); 

    -- Get feedback 
    instantiate_messages; 

    cdapi.close_activity(act_status); 

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

 

If you have taken a close look at the code, you may have noticed that this utility can very easily be enhanced to perform a lot more than a simple search/replace. The only place that you have to change for this is function abbrv_name. You may for example make sure that the name of  all 'DATE' columns like 'DATE_ORDER_TAKEN' end in '_DT': 'ORD_TAKEN_DT'.

If you have really analyzed the code, you may think that this utility could have a problem with columns containing several words to be replaced, e.g. 'PURCHASE_ORDER_NUMBER'. You may doubt my seriousness no more, I have thought about it too. This is why I select the column again after each update:
ciocolumn.sel(cur_guilty_column.col_id ,col);

A number of times I encountered the error that follows when running this script:
RME-00011: Operation 'upd' on COLUMN has failed
CDA-01003: <no message text found>
Activity aborted with API errors
The first time I was confronted with that error was because I had accidentally typed the character <TAB> in one of me abbreviated column names in table case_abbrev. The second time, I abbreviated DESCRIPTION as DESC, which is a reserved Oracle word that must not be used as a column name. In both cases, I simply update table case_abbrev and moved on.
If you get the same error, you will need to locate the offending value. The quickest way to do this is to un-comment the 2 debug lines of code in the middle of the script and run it again.
 
 

Please send me comments or suggestions:  herve@iherve.com
 Take a look at my HomePage