Oracle APEX – Build Configurable Help Option – Easy, Fast and Low Code

As part of Blog of Week, here is the first blog. This blog is about How you can easily setup customizable & configurable help for your Oracle APEX application.

As your application grows, you often need a way to setup documentation and help for your application users. Oracle APEX by default provides help text option with most of the component.

Here I am going to show you, how we can make it customizable and easy configurable by storing them in your custom table.

Time needed: 45 minutes.

To start with, following are the basic components needed:

  1. Create tables

    DEMO_HELP_TEXT
    This table to store help content. Could be plain text or html – based on your preference.

    DEMO_HELP_FILES
    If you prefer HTML help content (which is common), we often need to store images/screenshots along with your help content.

  2. Install plugins

    APEX Clob to Load
    This plugin will help us to setup large content help.

    APEX Unleash RichTextEditor
    This plugin will help us to store Images and Screenshots along with help content, which we often need.

  3. Create common APEX page for Help Text

  4. Configure Help Text buttons

So, let’s start by creating two tables as below.

CREATE table "DEMO_HELP_TEXT" (
    "ID"           NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    "APEX_APP_ID"  NUMBER NOT NULL,
    "APEX_PAGE_ID" NUMBER NOT NULL,
    "HELP_CODE"    VARCHAR2(50) NOT NULL,
    "HELP_CONTENT" CLOB,
    "CREATED_BY"   VARCHAR2(100),
    "CREATED_ON"   DATE,
    "UPDATED_BY"   VARCHAR2(100),
    "UPDATED_ON"   DATE,
    constraint  "DEMO_HELP_TEXT_PK" primary key ("ID")
);

CREATE table "DEMO_HELP_FILES" (
    "ID"           NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    "HELP_TEXT_ID" NUMBER NOT NULL,
    "FILENAME"     VARCHAR2(500),
    "MIMETYPE"     VARCHAR2(500),
    "BLOB_CONTENT" BLOB,
    "LAST_UPDATED" DATE,
    "CREATED_BY"   VARCHAR2(100),
    "CREATED_ON"   DATE,
    constraint  "DEMO_HELP_FILES_PK" primary key ("ID")
);


ALTER TABLE "DEMO_HELP_FILES" ADD CONSTRAINT "DEMO_HELP_FILES_FK" 
FOREIGN KEY ("HELP_TEXT_ID")
REFERENCES "DEMO_HELP_TEXT" ("ID")
ON DELETE CASCADE;

Now, lets start creating help configuration page. Below are the overview of steps:

  • Create new modal popup page. e.g. page 999
    • This page will be responsible to show and edit help information.
    • Configurations:
      • Create following Regions
        • Help
          • Type – Static
          • Template – Blank with Attributes
          • Position – Content Body
        • Display
          • Parent – Help
          • Type – Static
          • Template – Blank with Attributes
          • Position – Content Body
          • Static ID – showHelp
          • Server Side Condition
            • Item != Value
            • P999_EDIT
            • 1
        • Edit
          • Parent – Help
          • Type – Static
          • Template – Blank with Attributes
          • Position – Content Body
          • Server Side Condition
            • Item = Value
            • P999_EDIT
            • 1
        • Button
          • Position – Dialog Footer
          • Template – Buttons Container
      • Create Following Items
        • Help Region
          • P999_ID (Hidden)
          • P999_APEX_APP_ID (Hidden)
          • P999_APEX_PAGE_ID (Hidden)
          • P999_HELP_CODE (Hidden)
          • P999_EDIT (Hidden)
        • Edit
          • P999_HELP_CONTENT (Rich Text Editor)
      • Create Following Buttons
        • Buttons – Region
          • Save Button
            • Name – SAVE
            • Position – Create
            • Action – Submit Page
            • Server Side Condition
              • Item = Value
              • P999_EDIT
              • 1
          • Edit Button
            • Name – EDIT_MODE
            • Position – Change
            • Action – Redirect to Page 999
              • Set Items
                • P999_EDIT = 1
            • Server Side Condition
              • Item = Value
              • P999_EDIT
              • 0
          • Close Button
            • Name – CLOSE
            • Position – Close
            • Action – Defined by DA
      • Create Following Dynamic Actions
        • Name – Load Help Content into RTE
        • Event – Page Load
        • Server Side Condition
          • Item = Value
          • P999_EDIT
          • 1
        • True Action
          • Action – APEX Unleash RichTextEditor [Plug-in]
          • Execute on Image Upload
            • Ignore this error and save the source – ORA-06550: line 4, column 28: PLS-00382: expression is of wrong type
          • Execute to get Image Source
            • Ignore this error and save the source – ORA-06550: line 18, column 23: PLS-00382: expression is of wrong type
          • Affected Elements
            • Items
            • P999_HELP_CONTENT
          • Fire on Initialization – Off
      • Create Following Dynamic Actions
        • Name – Close Dialog
        • Event – Click
        • Selection Type = Button
        • Button = CLOSE
        • True Action
          • Action – Close Dialog
          • Fire on Initialization – Off
      • Create Following Dynamic Actions
        • Name – Render Help Text
        • Event – After Refresh
        • Selection Type = Region
        • Region = Display
        • Server Side Condition
          • Item != Value
          • P999_EDIT
          • 1
        • True Action
          • Action – APEX CLOB Load 2 [Plug-in]
          • Function Type – Print CLOBs
          • SQL Source
          • Escape special Characters – Off
          • Use Image Loader of Unleash RichTextEditor Plug-in – On
          • Execute to get Image Sources
            • Ignore this error and save the source – ORA-06550: line 22, column 23: PLS-00382: expression is of wrong type
          • Fire on Initialization – On
      • Create Following Process
        • Create Help Text
          • Type – PL/SQL
          • Point – After Header
          • Code
        • Update Help Text
          • Type – PL/SQL
          • Point – Processing
          • Server side condition
            • When Button Pressed
            • SAVE
          • Code

Load Help Content into RTE – Execute on Image Upload

DECLARE
    VR_FILE_NAME   VARCHAR2(200) := :FILE_NAME;
    VR_MIME_TYPE   VARCHAR2(200) := :MIME_TYPE;
    VR_BLOB        BLOB := :BLOB;
    L_HELP_TEXT_ID NUMBER := V('P999_ID');
    L_ID           NUMBER;
BEGIN
    INSERT INTO DEMO_HELP_FILES(
        FILENAME
       ,MIMETYPE
       ,BLOB_CONTENT
       ,HELP_TEXT_ID
    )
    VALUES(
        VR_FILE_NAME
       ,VR_MIME_TYPE
       ,VR_BLOB
       ,L_HELP_TEXT_ID
    ) RETURNING ID INTO L_ID;


    :P_OUT_PK   := L_ID;
EXCEPTION
    WHEN OTHERS THEN
        APEX_DEBUG.ERROR(SQLERRM);
        APEX_DEBUG.ERROR(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Load Help Content into RTE – Execute to get Image Source

DECLARE
    VR_FILE_NAME     VARCHAR2(200);
    VR_MIME_TYPE     VARCHAR2(200);
    VR_BINARY_FILE   BLOB;
    VR_PK            VARCHAR2(200) := :PK;
BEGIN
    SELECT FILENAME
          ,MIMETYPE
          ,BLOB_CONTENT
      INTO VR_FILE_NAME
          ,VR_MIME_TYPE
          ,VR_BINARY_FILE
      FROM DEMO_HELP_FILES
     WHERE ID = VR_PK;


    :FILE_NAME     := VR_FILE_NAME;
    :MIME_TYPE     := VR_MIME_TYPE;
    :BINARY_FILE   := VR_BINARY_FILE;
EXCEPTION
    WHEN OTHERS THEN
        APEX_DEBUG.ERROR(SQLERRM);
        APEX_DEBUG.ERROR(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Render Help Text – SQL Source

SELECT
    /* Element type dom - for jQuery selector e.g. body or #region-id, item - for item name e.g. P1_MY_ITEM */
    'dom' AS ELEMENT_TYPE,
    /* jQuery selector or item name */
    '#showHelp' AS ELEMENT_SELECTOR,
    /* CLOB value */
    help_content AS CLOB_VALUE
  FROM demo_help_text
 where id = :P999_ID

Render Help Text – Execute to get Image Sources

DECLARE
    VR_FILE_NAME     VARCHAR2(200);
    VR_MIME_TYPE     VARCHAR2(200);
    VR_BINARY_FILE   BLOB;
    VR_PK            VARCHAR2(200) := :PK;
BEGIN
    SELECT
        FILENAME,
        MIMETYPE,
        BLOB_CONTENT
    INTO
        VR_FILE_NAME,
        VR_MIME_TYPE,
        VR_BINARY_FILE
    FROM
        DEMO_HELP_FILES
    WHERE
        ID = VR_PK;

    :FILE_NAME     := VR_FILE_NAME;
    :MIME_TYPE     := VR_MIME_TYPE;
    :BINARY_FILE   := VR_BINARY_FILE;
EXCEPTION
    WHEN OTHERS THEN
        APEX_DEBUG.ERROR(SQLERRM);
        APEX_DEBUG.ERROR(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Create Help Text – Process PL/SQL

BEGIN
  FOR I IN(
      SELECT * 
        FROM DEMO_HELP_TEXT 
       WHERE APEX_APP_ID = :P999_APEX_APP_ID
         AND APEX_PAGE_ID = :P999_APEX_PAGE_ID
         AND HELP_CODE = :P999_HELP_CODE
  ) LOOP
    :P999_ID := I.ID;
  END LOOP;
  IF :P999_ID IS NULL THEN  
    INSERT INTO DEMO_HELP_TEXT(
        APEX_APP_ID
       ,APEX_PAGE_ID
       ,HELP_CODE
    )
    VALUES(
        :P999_APEX_APP_ID
       ,:P999_APEX_PAGE_ID
       ,:P999_HELP_CODE
    )
    RETURNING ID INTO :P999_ID;
  END IF;
END;

Update Help Text – PL/SQL Source

update demo_help_text
   set help_content = :P999_HELP_CONTENT
 where id = :P999_ID;
That’s it.
 
Checkout working demo here.
 
Hope this helps!
 
Regards,
Jaydip Bosamiya
jbosamiya@gmail.com

1 thought on “Oracle APEX – Build Configurable Help Option – Easy, Fast and Low Code

  1. Pingback: Launching apexpert.in - Integrated solutions using Oracle APEX and Native Mobile App - APEXPERT - Oracle APEX and Mobile App Consultants

Leave a Reply

Your email address will not be published.