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

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

• Next, you just need to create a button or link that navigates to page 999 with few details. Following is one sample implementation on demo page.
  • Button – PAGE_HELP
    • Action – Redirect to page in this application
    • Page – 999
    • Set items
      • P999_APEX_APP_ID – &APP_ID.
      • P999_APEX_PAGE_ID – &APP_PAGE_ID.
      • P999_HELP_CODE – PAGE_HELP
    • Clear cache – 999
  • Button – REGION_HELP
    • Action – Redirect to page in this application
    • Page – 999
    • Set items
      • P999_APEX_APP_ID – &APP_ID.
      • P999_APEX_PAGE_ID – &APP_PAGE_ID.
      • P999_HELP_CODE – REGION_HELP
    • Clear cache – 999
  • Button – ITEM_HELP
    • Action – Redirect to page in this application
    • Page – 999
    • Set items
      • P999_APEX_APP_ID – &APP_ID.
      • P999_APEX_PAGE_ID – &APP_PAGE_ID.
      • P999_HELP_CODE – ITEM_HELP
    • Clear cache – 999

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;