I. Michael Snyder

Dulcian, Inc.

Reference tables are easy to build. In any given system they tend to look and feel the same. In a fully normalized data model, reference tables typically represent 40-60% of the total number of tables. Therefore, even though the development effort per table is insignificant, the entire task can consume a substantial time for application development. This effort exists whether you are developing using the Designer/2000 or Developer/2000 tool set.

Wouldn’t it be great to not have to be bothered with this highly repetitive process ever again?

Wouldn’t it be wonderful to have these forms produced instantaneously with the press of a button?

Wouldn’t it be useful to have a way to add new reference table maintenance applications without any knowledge of Forms?

This paper presents the techniques used to build a fully dynamic, reference table maintenance form. Once built, this application can satisfy 100% of the requirements for maintaining reference (and sample) data, as well as adding or maintaining additional reference table requirements.

The techniques presented here are also applicable to other development efforts. The effort expended in creating modular, generic and dynamic components can be extended to many other tasks.

1. Maintain any typical reference table, supporting insert, update, and delete of data.
2. Validate simple foreign key relationships, supporting lookup capability.
3. Allow sorting by any field.
4. Identify mandatory fields

1. Generate default requirements from Designer/2000

1. Extract Tables and Titles from Des2k
2. Extract Column, prompts, and display properties from Des2k
3. Support updates and additions from Des2k

1. Support non-Des2k requirements

The above techniques will be covered in this paper and discussed in the presentation.

If time permits, the above planned enhancements will be discussed in the presentation.

Two tables were created to support retaining appropriate information for the dynamic maintenance of reference tables. REF_TABLE contains information about the tables to be maintained and serves as a list of valid tables for selection.

Create Table REF_TABLE

( Table_Name Varchar2(30) -- Name of table in the Database

, Title Varchar2(60) -- Title for the table in the Application

, Activ_YN Varchar2(1)); -- Flag to identify if table is used

REF_COLUMN contains information about the columns maintained for each table and provides format and validation criteria.

Create Table REF_COLUMN

( Table_Name Varchar2(30) -- Table name in the Database

, Column_Name Varchar2(30) -- Column name in the database

, Title Varchar2(60) -- Prompt for display in the Application

, Disp_Seq Number(10) -- Sequence column is displayed in Application

, Disp_Type Varchar2(30) -- Display type for the column (see below)

, Default_Value Varchar2(30) -- Default value when creating new record

, Value_List Varchar2(250) -- Static record group values

, Fk_Table_Name Varchar2(30) -- Foreign key table for lookup

, Fk_Column_Name Varchar2(30) -- Foreign key code for lookup

, Fk_Disp_Column_Name Varchar2(30) -- FK Column to display in Lookup

, Activ_YN Varchar2(1)); -- Flag to identify if column used

The display types supported by the application are as follows:

CS – Character Item Short

CL – Character Item Long

NM – Number Item

DT – Date Item

CB – Check Box

PF – Poplist to Foreign Key

PFN – Poplist to Numeric Foreign Key

PV – Poplist of Static Values

The Reference Maintenance form is composed of two Tabs. The first is used to maintain the reference tables (Maintenance). The second is used to set-up and maintain the reference table definitions (Setup).

The Maintenance tab is has three components. The first is a poplist to select the table that is to be maintained with a default value passed in as a parameter. Second, the prompts identify the columns in the database and serve as clickable items to perform sorts. The third component consists of the multi-record items for actual data-entry.

The Setup tab is composed of three blocks:

• The Master REF_TABLE block that displays table setup information
• The Detail REF_COLUMN block displays column setup information
• An Action block that contains buttons that perform global changes to the Setup tables including API utilities against the Designer/2000 repository.

A poplist is provided to allow the user to select a table to maintain. The poplist is populated from the tables in REF_TABLE using the Title as the Display where the Activ_YN is equal to ‘Y’. This poplist is populated in the WHEN-NEW-FORM-INSTANCE trigger and WHEN-MOUSE-CLICK trigger on the tab.

A Record Group can be dynamically created at run-time using a SQL statement. This record group can then be used in a poplist, a List-of-Values, or elsewhere in your application. The following procedure creates a Record Group.

PROCEDURE SETUP_RG

(i_rgname in varchar2 -- The name for the record group

, i_sql in varchar2) IS -- The SQL statement for this RG

-- >>> Setup new record group (from SQL) <<< --

BEGIN

-- >> Delete old RG << --

v_rg_id := Find_Group(i_rgname ); -- Get Internal ID for this RG

IF NOT Id_Null(v_rg_id) THEN -- Test for returned value

Delete_Group(v_rg_id ); -- Delete old version of record group

END IF;

-- >> Initialize RG with SQL << --

v_rg_id := Find_Group(i_rgname); -- Get Internal ID for RG

IF Id_Null(v_rg_id) THEN -- Be sure RG deleted above

v_rg_id := Create_Group_From_Query(i_rgname, i_sql);

-- Create RG from SQL

v_status := Populate_Group(v_rg_id );

-- Populate the RG

v_rowcount := Get_Group_Row_Count(v_rg_id);

-- Get a count of rows returned

ELSE

raise form_trigger_failure; -- Raise if not created or populated

END IF;

END SETUP_RG;

The poplist can then be linked to the record group using the following code:

populate_list(v_item, v_rg_id);

Oracle Forms does not support the dynamic creation of items. It is therefore necessary to anticipate the required needs of the reference table application. To dynamically change the number and types of items needed for the various reference tables, five of each of the item types were created in the form. This number can easily be increased to support differing needs. Each item is mapped to the proper column in the database using a view as discussed below.

Initially all items are set to "Non-displayed." Upon selection of a table to be maintained, the application loops through the columns to be displayed and changes the properties of the mapped item type to "Enabled," adjusts the X coordinates, and sets up the default navigation.

Procedure enable

( i_type varchar2 -- Item type

, i_num integer) is -- Nth item of this type

v_item varchar2(64) := 'maint.item_'||i_type||to_char(i_num);

Begin

set_item_property(v_item, query_only, property_false);

set_item_property(v_item, displayed, property_true);

set_item_property(v_item, enabled, property_true);

set_item_property(v_item, queryable, property_true);

set_item_property(v_item, updateable, property_true);

set_item_property(v_item, navigable, property_true);

set_item_property(v_item, position, g_xpos, g_ypos);

set_item_property(g_last_fld, next_navigation_item, v_item);

set_item_property(v_item, previous_navigation_item, g_last_fld);

. . .

g_width := get_item_property(v_item,width);

g_xpos := g_xpos + g_width;

End;

Radio buttons were selected as prompts, because they provide a visible clue that the current (radio checked) column is the sort field. To expand upon this capability a WHEN-MOUSE-DOUBLECLICKED trigger was added to reverse the sort by appending ‘desc’ or removing it. For this purpose, a single radio group was created with 40 buttons corresponding to each of the items. Additionally, the column is looked up in the database schema to determine if it is mandatory and the color of the prompt is changed as a visual clue.

Procedure prompt

( i_type varchar2 -- Item type

, i_title varchar2 -- Prompt

, i_table varchar2 -- Table Name

, i_column varchar2) is -- Column Name

v_item varchar2(64) := 'stuff.prompt';

v_radio varchar2(64) := 'pr_'||i_type;

v_width integer;

v_xpos integer;

Begin

set_radio_button_property -- Display

(v_item,v_radio,displayed,property_true);

set_radio_button_property -- Change to Column Title

(v_item,v_radio,label,nvl(i_title,'Label'));

set_radio_button_property -- Reset X and Y position

(v_item,v_radio,position,g_xpos-g_width,g_ypos-11);

v_width := pixel(i_title)+17; -- Revise width and adjust next

-- column if appropriate

if v_width > g_width then

v_xpos := g_xpos-g_width + v_width;

g_xpos := v_xpos;

g_width := v_width;

end if;

set_radio_button_property

(v_item,v_radio,width,g_width);

if mandatory(i_table, i_column) then

set_radio_button_property -- Change color of prompt when

-- manditory

(v_item,v_radio,visual_attribute,'data_rqrd');

else

set_radio_button_property

(v_item,v_radio,visual_attribute,'data_display');

end if;

End;

The DEFAULT_WHERE property of a block is changed only in the Setup component of this form. However, in support of dynamic sorting of the reference table, the following code is included in the WHEN-MOUSE-DOUBLECLICK trigger:

v_order := get_block_property('maint',order_by);

if v_order like '% DESC' then

set_block_property('maint',order_by,:stuff.prompt);

else

set_block_property('maint',order_by,:stuff.prompt||' DESC');

end if;

fp_block.query_block('maint');

To allow default functionality for query, insert, update, and delete data, the Reference Table Maintenance Application uses a simple one-table view. This view is dynamically recreated in the user’s Oracle account space as an alias to the table selected to be maintained. To change the view dynamically, Forms_DDL is used to run a dynamic CREATE SQL statement. To allow the view to function properly in the form, unused columns are mapped to null, to_number(null), or to_date(null).

procedure bld_vw is

cursor c_col is -- Cursor to select columns

select column_name, title, disp_type, fk_table_name tab,

fk_column_name key, fk_disp_column_name disp

from ref_column

where table_name = :stuff.ref_table

and activ_yn = 'Y'

order by disp_seq;

v_cnt integer := 0;

v_fld varchar2(64);

v_type varchar2(64);

Begin

view_sql := 'Create or replace view v$ref_maint as select';

for cr in c_col loop -- Loop through all columns

v_cnt := v_cnt +1;

if v_cnt > 1 then

view_sql := view_sql||',';

end if;

v_type := dtype(substr(cr.disp_type,1,2));

v_fld := 'item_'||v_type;

view_sql := view_sql||' '||

cr.column_name||' '||v_fld;

end loop;

view_sql := view_sql||null_cols;

-- Add all null columns

view_sql := view_sql||' from '||:stuff.ref_table;

forms_ddl(view_sql);

End;

To optionally support calling up a desired reference table to maintain, parameters are used to pass the name of a table. This table is then set as the default value for the poplist lookup. Additional code could be added to hide the poplist in order to give the impression that this application is specific for the table requested.

In the WHEN-NEW-FORM-INSTANCE trigger:

copy(upper(name_in('parameter.in_table')),'stuff.ref_table');

if :stuff.ref_table is not null then

p_ref.set_up(:stuff.ref_table);

end if;

The Setup routine is a very simple master-detail form relating REF_TABLE to REF_COLUMN with pop-lists or other simple validation techniques against the underlying database schema.

To support standard analysis using Designer/2000, a few simple API routines were written to access the Table and Column definitions stored in the CASE repository. To select the Designer/2000 application system, a poplist with a record group based on the following SQL statement provides the simplest API.

select id, name from ci_application_systems

where LATEST_VERSION_FLAG = 'Y'

Tables and Columns are easily populated using the following cursors against the API views.

cursor c_tab (i_app_id integer, i_tab_like varchar2) is

select display_title disp, name, id

from ci_table_definitions

where application_system_owned_by = i_app_id

and name like i_tab_like;

cursor c_col (i_tab_id integer) is

select name, prompt, display_sequence seq, datatype dtype

, maximum_length maxlen

from ci_columns

where table_reference = i_tab_id;

In conclusion, the development of a fully dynamic, reference table maintenance application is not a difficult process. The application can be simply built from standard components, using a variety of common and easy-to-implement techniques.