DOWNLOAD Oracle JDeveloper

TAGS forms, java, All

Return to Formsville

By Penny Cookson and Chris Muir

It’s still important to know the key architectural concepts common to many Oracle Forms–based applications

Published June 2008

New programmers moving into Oracle sites running legacy Oracle Forms and Oracle Designer applications face many challenges. New programmers rely heavily on their senior counterparts to teach them how to structure their applications, as well as the benefits of such structures from hard-earned experience. There are certainly plenty of best practices around for Oracle Forms, and Oracle Designer gave its own particular stamp to Oracle Forms architecture. Yet at many sites, senior programmers have moved on, leaving their successors guessing why applications are structured as they are. With the pressure to fix mounting maintenance issues, the elegance of the original solutions can be jeopardized.

For those starting their Oracle Forms journey, this article attempts to explain the key architectural concepts that are common to many Oracle Forms– and Oracle Designer–based applications, including their benefits and how you can use them to your advantage. In particular, the article will focus on:

• A simple Oracle Forms trigger architecture
• A flexible Corporate-Application-Form Oracle Forms library architecture
• Extending the approach to forms generated from Oracle Designer
• Oracle Designer artifacts, including the table API, cg_ref_codes, and journal tables.

A Simple Oracle Forms Trigger Architecture

It’s often surprising in computing that different developers working on separate problems will come up with the same best practice solution. Sometimes this is through hard-earned experience, clever programming, or just actively listening to best practices discussed by Oracle or at Oracle User Group events. One such common solution that appears in Oracle Forms circles is what we’ll refer to as the Oracle Forms trigger framework. This programmatic framework has proved to be useful from Oracle Forms version 4.5 right through to the latest version and can be found at many Oracle sites in one hybrid form or another.

In this section, we will describe the trigger framework and how it is structured, as well as its key benefits and disadvantages. For those who are familiar with Oracle Headstart, there are clear similarities between the two approaches.

Note: Oracle Headstart is a product similar in intention to Oracle JHeadstart (“Java” Headstart), with the former purely aimed at Oracle Designer and Oracle Forms and the latter supporting Oracle JDeveloper and Oracle Application Development Framework (Oracle ADF). At one time Oracle Headstart was a choice for many Oracle sites undertaking “100% generation,” a contemporary best practice technique for generating Oracle Forms applications from Oracle Designer at the time.

Goals

The goal of the trigger framework is threefold:

• Remove “trigger splatter”—in order to avoid that horrible situation where the previous programmer has coded millions of triggers sprayed over item, block, and form level, which can be terribly hard to debug and search.

• Increase the transparency of what trigger fires when, without having to remember every single trigger type peculiarity—especially important for junior programmers who aren’t familiar with Oracle Forms but have been tasked to maintain an existing Oracle Forms system.

• Reduce maintenance costs through easier debugging—thanks to the fact that the Oracle Forms debugger has been notoriously unusable in many versions. I find this one particularly important because I move between customers’ sites with different versions of Oracle Forms.

Implementing the framework

The framework has two rules:

• No item-level triggers; all item-level triggers are promoted to the block level.

• No code in the triggers, besides that calling a program unit.

The first rule implies that you shall not write item-level triggers; you must do it at the block level. The second rule implies that the block- or form-level trigger will call a program unit passing in the trigger/event name, as defined below.

The framework requires

• A package for every block—at least, a package for every block that has trigger code.

• A single package for the form—at least, a package for the form if it has trigger code.

For example, a form that has two blocks, ORGANISATIONS_BLK and EVENTS_BLK, and trigger code for the form and each block will require three packages, namely FORMS_PKG, ORGANISATIONS_BLK_PKG, and EVENTS_BLK_PKG.

Triggers are defined as follows:

Any trigger defined at form or block level—being cognizant of the two framework rules above that we don’t define item-level triggers—will look something like the following:

FORM.WHEN-NEW-FORM-INSTANCE trigger:

    form_pkg.event_handler('WHEN-NEW-FORM-INSTANCE');

BOOKINGS_BLK.WHEN-VALIDATE-ITEM trigger:

    bookings_blk_pkg.event_handler('WHEN-VALIDATE-ITEM');

Note how each package has a method event_handler, which we call and pass in the trigger name that has fired, which leads to our next point:

Each package requires:

• A single method handler of the following specification:

PROCEDURE event_handler(i_trigger IN VARCHAR2) IS ....

And now in the event_handler you have one large IF-ELSIF-ELSE-THEN—or CASE statement, depending on your PL/SQL version in Oracle Forms—to determine what trigger fired and what other procedure to call to handle the event.

Potentially, if the trigger is an item-level trigger within the event_handler procedure, you will also need to derive :system.cursor_item to work out what item fired the trigger so you know what code to execute.

Let’s take an example to help explain the above.

Say in our BOOKINGS_BLK, for both the STATUS and NUMBER_ATTENDEES items, we want to write code against the WHEN-VALIDATE-ITEM trigger on both of these fields to perform data validation.

Considering our trigger framework explained above, we don’t create two separate WHEN-VALIDATE-ITEM triggers for each item; instead, we create a single WHEN-VALIDATE-ITEM trigger at the BOOKINGS_BLK level, as follows:

bookings_blk_pkg.event_handler('WHEN-VALIDATE-ITEM');

Our event_handler routines would look something like the following:

PACKAGE BODY bookings_blk_pkg IS

  PROCEDURE event_handler(i_trigger IN VARCHAR2) IS

    k_trigger_item CONSTANT VARCHAR2(500) := :system.cursor_item;

  BEGIN

    IF i_trigger = 'POST-QUERY' THEN

       ...do something;

    ELSIF i_trigger = 'WHEN-VALIDATE-ITEM' THEN

      IF k_trigger_item = 'BOOKINGS_BLK.STATUS' THEN

         ...do something for status;

      ELSIF k_trigger_item = 'BOOKINGS_BLK.NUMBER_ATTENDEES' THEN

         ...do something for number_attendees;

      ELSE

        NULL; -- Do nothing; other items have no validation

      END IF;

    ELSE

      -- unhandled trigger; programmer error

      RAISE FORM_TRIGGER_FAILURE;    

    END IF;

  END event_handler;

END bookings_blk_pkg;

From the above code, it would be typical to make a procedure call when handling the STATUS or NUMBER_ATTENDEES field unless there is a minor amount of code. Any such procedure would remain in the same package, BOOKINGS_BLK_PKG, for logical grouping of code.

Debugging advantages

Besides the logical advantage of grouping like-minded code into packages, you may also take advantage of the following with this approach:

• The event_handler acts as a choke point for all trigger events. Regardless of whether you’re using the Oracle Forms debugger or good old message Forms built-in, you can place a break point or call to message at the beginning of the event_handler procedure to now work out what is firing when. This is especially helpful for a junior programmer, because it will make the firing order of the triggers immediately obvious.

• If your form is failing and you suspect it’s because of the interaction of a number of triggers, you can easily comment out the trigger hooks in the event_handler procedure, effectively disabling the code. Contrast this to having numerous WHEN-VALIDATE-ITEM triggers at item level across the forms; disabling them all with comments is a hard task, especially if you miss one.

Disadvantages

There are some disadvantages of this approach:

• If you delete a trigger call to the event_handler, you need also to delete the event_handler code. Otherwise, a dangling redundant event handler will be left in the code.

• There is a performance hit on this code, because it needs to make a procedure call and then traverse a (potentially) giant IF-ELSIF-ELSE-THEN structure in your event_handler procedure. In addition, for some items there may in fact be no code in the trigger handler to execute for the specific item, resulting in unnecessary processing.

Flexible Corporate-Application-Form Oracle Forms Library Architecture

The approach described above allows you to add form-specific code using a standard event handler mechanism, but what about code that needs to be standardized across multiple forms in your application, or even multiple applications across your organization? To achieve this, we use PL/SQL libraries.

Goals

We want all our forms to behave in a similar manner. We want triggers to execute standard corporate code by default.In addition, we want to allow Oracle Forms in a particular application to add application-level code to the corporate code or override the corporate code if necessary.

Implementing the framework

The framework requires the following structures:

• An application PL/SQL library is attached to every form.

• A corporate PL/SQL library is attached to every application PL/SQL library.

• A standard set of form-level triggers are defined in all our forms.  Each trigger calls the application-level event handler.

• By default, the event handler in the application library calls the event handler in the corporate library.

As an example, let’s consider an organization in which we want the following Oracle Forms behavior:

• Normal behavior on entry to any form is to execute a query. 

• For our Bookings application, we also want to execute some additional code to define privileges on entry to the Bookings form. 

• We want to disable the Edit key for all forms. 

Consider a form BK0010.fmb. This has an attached application library APPPLSTD.PLL, which in turn has an attached corporate-level library called COMPLSTD.PLL.

The WHEN-NEW-FORM-INSTANCE trigger in our BK0010 form contains the following code:

-- perform normal application processing

app_event_handler_pkg.handle_event('WHEN-NEW-FORM-INSTANCE');

The app_event_handler_pkg package in APPPLSTD.PLL will include the following code:

PACKAGE BODY app_event_handler_pkg IS

  PROCEDURE handle_event(i_trigger IN VARCHAR2) IS

  BEGIN

    IF i_trigger = 'WHEN-NEW-FORM-INSTANCE' THEN

      -- call an application specific procedure

      security_handler_pkg.set_block_privs;

      -- now call the standard corporate processing

      com_event_handler_pkg.handle_event(i_trigger);

    ELSIF i_trigger = 'KEY-EDIT' THEN

      -- no application specific code just call the corporate code

      com_event_handler_pkg.handle_event(i_trigger);

    ELSIF i_trigger = 'PRE-FORM' THEN

      -- no application specific code just call the corporate code

      com_event_handler_pkg.handle_event(i_trigger);

    ..... trap other standard trigger events

    .........................................

    ELSE

      -- unhandled trigger; programmer error     

      RAISE FORM_TRIGGER_FAILURE;   

    END IF;

  END handle_event;

END app_event_handler_pkg;

Note that the security_handler_pkg package is application-specific and would be defined in the application library APPPLSTD.PLL.

The app_event_handler_pkg package in COMPLSTD.PLL will include the following code:

PACKAGE BODY com_event_handler_pkg IS

  PROCEDURE handle_event(i_trigger IN VARCHAR2) IS

  BEGIN

    IF i_trigger = 'WHEN-NEW-FORM-INSTANCE' THEN

      -- execute the corporate processing

      initialise_pkg.perform_query;     

    ELSIF i_trigger = 'KEY-EDIT' THEN

      -- disable the normal key-edit behaviour

      NULL;

    ELSIF i_trigger = 'PRE-FORM' THEN

      -- no corporate code for this trigger event;

      NULL;

    ..... trap other standard trigger events

    .........................................

    ELSE

      -- unhandled trigger; programmer error

      RAISE FORM_TRIGGER_FAILURE;    

    END IF;

  END handle_event;

END com_event_handler_pkg;

Note that the initialise_pkg package is corporate-specific and would be defined in the corporate library COMPLSTD.PLL.

Advantages

This approach offers the following advantages:

• Standardization. The main advantage to this approach is the ability to standardize Oracle Forms processing across an organization or application. If you have a template form set up to use this trigger/library structure, then any form created from the template will have this standard processing already built in without the requirement for the developer to take any action.

• Maintenance. This approach also makes it very easy to add code to an entire application or organization. Any code added to the event handler in the application or corporate library is immediately applied to all forms. This is much easier than opening each form and adding additional code.

Disadvantages

There are some disadvantages to this approach:

• Any changes to the application or corporate libraries may have a significant impact on a large number of forms. It is essential that changes to these libraries are carefully controlled and rigorously tested prior to deployment.

• A developer can override the standard processing by creating triggers at the block or item level in the form. This is a deliberate feature of the approach, as in the example above, where we may want to enable the KEY-EDIT functionality for a specific item in a form. However, in some cases it can lead to the corporate or application processing being accidentally overridden. It is important that developers understand the structures and define the execution hierarchy of triggers carefully.

• The naming of program units is important. At compile time, Oracle Forms will first look for a program unit in the form, and then in attached libraries in the order in which they appear in the Application Navigator and then in the database. Ensure that program units at different levels in the library hierarchy have different names in order to prevent conflicts.

Extending the Approach to Forms Generated from Oracle Designer

Oracle Designer was a CASE tool of choice for many Oracle sites, but today has taken a back seat to relatively modern software development tools such as JDeveloper.  The aim of many developers when generating forms from Oracle Designer was to achieve “100% generation,” or at least to minimize post generation changes made to the forms. (For a time, the achievement of 100% generation became the holy grail of Oracle Designer and Oracle Forms development). An aid to achieving this aim was the extension of the library structure described above to a three-tier structure in which each form had its own PL/SQL library attached. This library contained form-specific code.

Implementation

Consider the BLK0010 form described in the previous example. Using the three-tier structure, this form would have its own library BK0010.PLL attached. This library contains

• A module event handler similar to that described for an application.

• All code specific to the form module.

• Calls to the application-level event handler

The WHEN-NEW-FORM-INSTANCE trigger in our BK0010 form contains the following code:

-- perform module specific processing

mod_event_handler_pkg.handle_event('WHEN-NEW-FORM-INSTANCE');

The mod_event_handler_pkg package in BK0010.PLL includes the following code:

PACKAGE BODY mod_event_handler_pkg IS

  PROCEDURE handle_event(i_trigger IN VARCHAR2) IS

  BEGIN

    IF i_trigger = 'WHEN-NEW-FORM-INSTANCE' THEN

      -- call a module specific procedure

      form_pkg.event_handler('WHEN-NEW-FORM-INSTANCE');

      -- now call the standard application processing

      app_event_handler_pkg.handle_event(i_trigger);    

    ELSIF i_trigger = 'KEY-EDIT' THEN

      -- no module specific code just call the application code

      app_event_handler_pkg.handle_event(i_trigger);

    ..... trap other standard trigger events

    .........................................

    ELSE

      -- unhandled trigger; programmer error

      RAISE FORM_TRIGGER_FAILURE;    

    END IF;

  END handle_event;

END mod_event_handler_pkg;

The packages described in the first section of this article, for example form_pkg, will reside in the form-specific library BK0010.PLL rather than in the form itself.

Advantages

The main advantage when using this structure is to effectively externalize changes to code. Because the form contains only “hooks” rather than the code itself, changes rarely need to be made to the form module. Instead, code is added to and changed in the module-specific library attached to the form.

Disadvantages

This approach is somewhat cumbersome to manage, because it may result in a large number of PL/SQL libraries, ultimately one for each form.

Oracle Designer Artifacts

Oracle Designer also provided a number of utilities to assist with the development process. These included the following:

Managing reference codes

Valid values for reference data were entered into Oracle Designer as domains. These domains could then be used to generate a reference code table. The table is named CG_REF_CODES or APP_REF_CODES (where APP is the application acronym), depending on a preference chosen in Oracle Designer. The table could be used for both valid values and valid ranges.

The structure of the table is as follows:

In Example 1, you would expect to see a number of rows for the COUNTRY domain. These would be used for validation and to populate pop-up lists

Example 2 is used to generate forms validation that ensures that any column in the WORKING_DAYS domain has values between 0 and 7.

APIs and triggers

Applications developed with Oracle Designer may include a set of packages and triggers for each table. In the following descriptions, a table called ORGANISATIONS has been used for all examples.

Table APIs

The table package is called cg$table_name (for example CG$ORGANISATIONS), and for each table it includes the following constructs:

Procedures are provided to perform all the standard DML operations and some additional validation.

Additional procedures are included, but they all follow a similar pattern.

The main procedures in the package set a global variable called_from_package to TRUE to indicate that the DML statement was initiated from the table API.

Advantages

Using the table API to perform all operations on a table results in all the business rules being located in a single place—ultimately, the database. If business rules are alternatively coded in an application’s middle tier or front end, then they might easily be missed when additions to the application are made or when direct access to the tables using other tools is allowed.

If users are granted execute privilege on the package but no direct privileges on the table, then we can guarantee that all changes to the table are made via the table API. This not only ensures that all validation is performed but also provides a central point for debugging.

Disadvantages

The approach is fairly verbose and results in large number of lines of code, which can confuse inexperienced developers.

From a performance perspective, these procedures perform DML on individual records. They were initially developed at a time when bulk processing using the FORALL clause was not available and therefore do not take advantage of the performance benefits that the clause provides.

Maintenance can be more difficult and time consuming. If the structure of a table is changed—for example, a column is added—then corresponding changes will need to be made to the procedures in the table API.

Triggers

In addition to the table API, in some cases Oracle Designer has been used to create a set of triggers for each table. The triggers are used when a DML statement accesses the table but the DML has not been initiated via the table API. In this case, the global variable called_from_package will be null.

Triggers are created at statement and row level for each operation, which requires some processing to occur. The code that is called mirrors that of the package.

The triggers are named according to their events and trigger level. Using the ORGANISATIONS table as an example, the update triggers are named as follows:
cg$BUS_ORGANISATIONS: Before, update statement-level trigger
cg$BUR_ORGANISATIONS: Before, update row-level trigger
cg$AUR_ORGANISATIONS: After, update row-level trigger
cg$AUS_ORGANISATIONS: After, update statement-level trigger

Advantages

Using these triggers ensures that business rules such as validation are applied regardless of the source of the DML statement. This prevents SQL that changes a table directly from bypassing validation code.

Disadvantages

These triggers will fire even where the processing has been initiated from the table API package. Once the code ascertains that the variable called_from_package is set to TRUE, the rest of the code will be bypassed. However, this still results in the execution of the trigger, with the associated performance overhead.

Maintenance can be more difficult and time consuming. If the structure of a table is changed—for example, a column is added—then corresponding changes will need to be made to the trigger code.

Module APIs

An extension to this approach uses a PL/SQL package as the datasource and target of each forms block. If this approach has been used, you will also find a package for each forms block as follows:

Package cgc$BK0020_ORGANISATIONS is used for operations on the ORGANISATIONS block in the BK0020 form. 

This package contains a set of procedures that accept a PL/SQL table of type cgc$rec_tab. This is a table of records that matches the fields contained in the forms block. The procedures are used to support the standard Insert (ins), Update (upd), Delete (del), Locking (Lck), and Query (qry) behavior of a forms block. Each procedure loops around the records in the PL/SQL table and then calls the appropriate procedure from the table API.

For example, to update a record, the Forms block passes changed records to cgc$BK0020_ORGANISATIONS.upd. For each record in the PL/SQL table passed to cgc$BK0020_ORGANISATIONS.upd, the procedure cg$ORGANISATIONS.upd is called to update the record.

The exception to this is the qry procedure which does not call a corrsponding table API.  It uses dynamic SQL to query the underlying table and return the results.

Advantages

This approach was generally only used for complex forms in which the processing performed by a block needed to be more complex than a simple DML statement on a single table. It allowed additional code to be added to the module API, which could then significantly enhance or bypass the normal Oracle Forms processing.

Disadvantages

This approach results in a two-tier API structure, which significantly increases the lines of code in the application.

Maintenance can be difficult when using this technique, because changes to the forms block—for example, the addition of a field—require changes to multiple procedures in each of the two levels of packages.

Journal tables

Oracle Designer provided a standard approach to creating journal tables, which audited all DML on a parent table, ultimately logging all data changes. Via a preference setting within Oracle Designer, when generating the table DDL a corresponding journal table was created. The structure of the journal table would match the columns in the original table, with the addition of some columns that audit the operation that made the change.

The journal table is normally populated using the insert_jn procedure of the table API described above.

Additional columns include

Advantages

This approach provided comprehensive journaling of the values in a DML statement with little effort from the developer, allowing disputes about who deleted or changed records to be easily answered.

Disadvantages

This approach relies on the use of the Oracle Designer table APIs and triggers. If these are removed, then journaling will not occur. Many sites have now replaced this approach with hand-crafted triggers on the tables that perform the insert into the journal tables.

Conclusion

Inheriting a legacy system can be a daunting task when there are obvious layers of code but little experience in or understanding about why the previous development team put them in place. Oracle Forms and Oracle Designer have been and still are significant tools in building large-scale corporate systems. As a result, best practices are commonly seen in many systems yet little appreciated by junior programmers without the appropriate background. An understanding of these best practices can see them maintained for the future life of the system without diving into the code for seemingly easy one-off fixes.

Penny Cookson is an Oracle ACE and Oracle Educator of the Year 2004 (oracle.com/technology/oramag/oracle/04-nov/o64awards.html#COOKSON) and managing director of SAGE Computing Services (www.sagecomputing.com.au) in Australia.

Chris Muir (http://one-size-doesnt-fit-all.blogspot.com) is an Oracle ACE Director (Oracle Fusion Middleware) and a senior consultant and Oracle trainer for SAGE Computing Services in Australia. With a combined 40 years of experience in Oracle development and database technology, both show battle scars with years of experience in the RDBMS, traditional Oracle development tools, as well as Oracle Application Express, Oracle JDeveloper, and good old SQL*Plus.