User Customisable Prompts

A HunBug Document
Document No.: 11280
Forms Version: Oracle Forms 6i +
Last Updated: 01 Oct 2006
Author: HunBug

With prompt properties replacing the need for boilerplate text, we can now modify the value of the prompt at runtime. This article looks at storing prompt text in the database, so they can easily be customised by users for each installation. For this article we have made the code generic but it can easily be tailored for system specific requirements or enhancements.

Table
The table stores the form, block and column names along with the prompt text. This can then be referenced from the when new form instance triggers, to display the modified prompt text. Indexes, constraints and tablespaces should be added as required.

CREATE TABLE custom_prompts
( formName        VARCHAR2(50)  NOT NULL,
  blockItem       VARCHAR2(100) NOT NULL,
  promptText      VARCHAR2(30)  NOT NULL );


Form
To change a prompt value the user should place the cursor in the item to be changed, then select an option from the menu which calls the customise routine. On selecting the menu option, a window is displayed showing a text item containing the existing customised prompt value, null for the forms prompt property, allowing the user to change one prompt at a time without having to know the technical information about the item. The window also contains OK and Cancel buttons.

The form requires a block to enable the user to change the prompt. This can either reside in the form itself or in its own form that can be called from many forms within an application. In this example we use the later example.

Items for the block "CTL_CUSTOMISE".

TI_PROMPTTEXT TEXT ITEM Text item which allows the prompt text to be set.
PB_OK PUSH BUTTON The button to OK the change to the prompt and perform the update
PB_CANCEL PUSH BUTTON Return to the calling position without making changes

Items set during the menu item call to the "CTL_CUSTOMISE" block.

:CTL_CUSTOMISE.TI_PROMPTTEXT Queried from the custom_prompts table. A null value should indicate the default form prompt is used.
:PARAMETER.formName The form name, taken from GET_APPLICATION_PROPERTY(FORM_NAME)
:PARAMETER.blockItem The block and item name, taken from :SYSTEM.CURSOR_ITEM

When button pressed trigger for PB_OK.
BEGIN
   
    dpr_customise
    ( p_formName    => :PARAMETER.formName,
      p_blockItem   => :PARAMETER.blockItem,
      p_prompt      => :ctl_customise.ti_prompt );
   
  EXIT_FORM;
   
END;
The cancel button trigger would simply exit form.

Stored Procedure
A stored procedure is used to make the database changes so that it can use the autonomous transaction clause, committing the changes immediately. The procedure assumes a prompt format of: an optional colon when the prompt is to the side and an asterisk to the left of the prompt to indicate mandatory items.
CREATE OR REPLACE PROCEDURE dpr_customise
( p_formName     IN VARCHAR2,
  p_blockItem    IN VARCHAR2,
  p_promptText   IN VARCHAR2 ) IS

  v_promptText   VARCHAR2(30) := LTRIM(RTRIM(p_promptText,':'),'*');

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  IF p_promptText IS NULL THEN
    DELETE FROM custom_prompts
     WHERE formName   = p_formName
       AND blockItem  = p_blockItem;
  ELSE
    UPDATE custom_prompts
       SET prompt     = v_promptText
     WHERE formName   = p_formName
       AND blockItem  = p_blockItem;
    IF SQL%ROWCOUNT = 0 THEN
      INSERT INTO custom_prompts
      ( formName, blockItem, prompt )
      VALUES
      ( p_formName, p_blockItem, v_promptText );
    END IF;
  END IF;

  COMMIT;

END;
/

When New Form Instance
The procedure below should be called from the when new form instance trigger. It can live as a program unit in the form but is most suitable for a common library, especially if used by many forms. The procedure loops through the custom prompts for the form and sets the item property where customised prompts are found.
PROCEDURE lpr_customPrompts IS

  c_formName         CONSTANT VARCHAR2(50) := GET_APPLICATION_PROPERTY(FORM_NAME);

  v_item             ITEM;
  v_currentPrompt    VARCHAR2(100);
  v_newPrompt        VARCHAR2(100);
 
  CURSOR cur_customPrompts(p_formName VARCHAR2) IS
  SELECT blockItem,
         promptText
    FROM custom_prompts
   WHERE formName = p_formName
     AND promptText IS NOT NULL;
 
BEGIN

  FOR cp IN cur_customPrompts(c_formName) LOOP
    v_item := FIND_ITEM(cp.blockItem);
    IF NOT ID_NULL(v_item) THEN
      v_currentPrompt := GET_ITEM_PROPERTY(v_item,PROMPT_TEXT);
      v_newPrompt     := cp.promptText;
      IF SUBSTR(v_currentPrompt,1,1) = '*' THEN
        v_newPrompt := '*'||v_newPrompt;
      END IF;
      IF SUBSTR(v_currentPrompt,-1) = ':' THEN
        v_newPrompt := v_newPrompt||':';
      END IF;
      SET_ITEM_PROPERTY(v_item,PROMPT_TEXT,v_newPrompt);
    END IF;
  END LOOP;

END;

Enhancements
This is a generic example which can be modified to suitable application needs, such as:
  • Adding the username, so each user can have there own prompts.
  • Adding the nls_language to give language specific prompts
  • A similar set of procedures could be created for hint text.

Further Reading


HunBug Store








Comments are all individually read, none are automatically posted to the site.