ApEx: Manage Item Help Texts

Recently a customer asked me if he could specify or manage his own help labels. My first reaction was to give him access to my development environment and explain how he could set the help texts for each label. After some more thinking I think anyone would see that this is not really the best solution, what if he accidently deletes items or other components?

Time to work out a custom solution. I started by searching how the help texts for items are saved on the flow user. I found out that if you write a help text, a record is inserted in the wwv_flow_step_item_help table. This record refers to a record in the wwv_flow_step_item table, which is the actual item.

With this information I could make a view and work with an ‘instead of’ trigger to update/insert the help texts. Let’s start with the view, I need all items that are not hidden and order them by page and region and display sequence.

CREATE OR REPLACE FORCE VIEW “V_APEX_ITEM_HELP_TEXT” (“FLOW_ITEM_ID”, “FLOW_ID”, “ITEM_NAME”, “ITEM_LABEL”, “PAGE_ID”, “PAGE_NR”, “REGION_NAME”, “ITEM_HELP_ID”, “ITEM_HELP_TEXT”) AS
SELECT item.id flow_item_id,
item.flow_id flow_id,
item.name item_name,
item.prompt item_label,
item.flow_step_id page_id,
item.item_sequence page_nr,
region.plug_name region_name,
item_help.id item_help_id,
item_help.help_text item_help_text
FROM flows_030000.WWV_FLOW_STEP_ITEMS item,
flows_030000.WWV_FLOW_PAGE_PLUGS region,
flows_030000.WWV_FLOW_STEP_ITEM_help item_help
WHERE item.flow_id = v(‘APP_ID’)
AND item.item_plug_id = region.id
AND item.id = item_help.flow_item_id(+)
AND display_as NOT IN (‘HIDDEN’)
ORDER BY flow_step_id, plug_display_sequence, item_sequence;

We now need the trigger that will update or insert the help text for an item:

create or replace TRIGGER V_APEX_ITEM_HELP_TEXT_BIUD
INSTEAD OF
INSERT OR DELETE OR UPDATE
ON V_APEX_ITEM_HELP_TEXT
REFERENCING OLD AS OLD NEW AS NEW
BEGIN
IF inserting OR deleting THEN
raise_application_error(‘-20010′,’delete are insert not allowed’);
ELSIF updating THEN
——————————————————————————
— insert help text
——————————————————————————
IF :new.item_help_id IS NULL THEN
INSERT INTO flows_030000.wwv_flow_step_item_help ( flow_id,
flow_item_id,
help_text)
VALUES (:new.flow_id,
:new.flow_item_id,
:new.item_help_text);
——————————————————————————
— update help text
——————————————————————————
ELSE
UPDATE flows_030000.wwv_flow_step_item_help
SET help_text = :new.item_help_text
WHERE id = :new.item_help_id;

END IF;
END IF;
END;

The application in question has more than 100 pages so I will give my customer the option to first select the page and then fill in the help items. I started by making a page and create a new item (P9500_PAGE_ID) with type select list with submit, in the list of values source I have the next query:

SELECT page_id || ‘ – ‘ || page_title display_value,
page_id return_value
FROM APEX_APPLICATION_PAGES
ORDER by page_id

The query will list all my pages in my select list. Now I made an updateable report based on my V_APEX_ITEM_HELP_TEXT view.

All done, the instead of trigger will perform the necessary action to save the help texts.

Although this solution has a lot of pro’s and contra’s, this proves again how flexible we can work with ApEx.

About these ads

8 thoughts on “ApEx: Manage Item Help Texts

  1. It would be better if Apex exposed a public API in PL/SQL for doing updates to its metadata.Then your instead-of trigger could call the API instead of doing direct table updates, and be reasonably sure that something didn’t break in the next version of Apex (and that any business rules and validations the Apex team choose to put in the API are always run).

  2. In fact, I would advise you to create your own API packages to wrap the Apex metadata updates, so that you only have to change your implementation once if you use this (and similar techniques) in multiple places.

  3. Hi, i do my page but i have an error:Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = “4A735D418285CEC1DB3318F264531E4F”, item checksum = “046A649A1ACE016C6B378E7F1F73F63B”., update “PRESCRM_DEV”.”WW_APEX_ITEM_HELP_TEXTW” set “FLOW_ITEM_ID” = :b1, “FLOW_ID” = :b2, “ITEM_NAME” = :b3, “ITEM_LABEL” = :b4, “PAGE_ID” = :b5, “PAGE_NR” = :b6, “REGION_NAME” = :b7, “ITEM_HELP_ID” = :b8, “ITEM_HELP_TEXT” = :b9any help?Thanks in advanceCostantino

  4. Thank you for this. I was looking for a quick way to update the help text in my app (cos doing it item by item is a real pain) so I’ve used your idea to build a quick form for internal use only. You’ve saved me lots of time – and by extension, I guess, you’ve saved the company I work for some money.

  5. Hi, I am looking into this and wanted to know which oracle user you logged on as to create the views as I can create the first view as SYS but cannot grant access to anyone else to the view. Any ideas?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s