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
|
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') / |
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 ******************************************************
set arraysize 1
spool guilty_col
-- Report on "Guilty" Columns
spool out |
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
v_app_id number(38);
cursor guilty_columns (p_app_id number) is
function abbrv_name(
procedure instantiate_messages is
begin -- Get Application ID
-- Initialize API if not already done
-- Set DBMS Output Buffer to Max Size
for cur_guilty_column in guilty_columns(v_app_id) loop
-- Correct the column name.
-- Validate the update
-- Get feedback
cdapi.close_activity(act_status); -- If the activity did not close successfully,
roll back
-- Otherwise, this must have been an ORACLE SQL
or internal error so
|
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