Thursday, October 11, 2012

Dynamically changing item labels

Today I’ve got one idea how to simply change (in my example translate) item label dynamically with value from database using shortcuts.

First of all you have to create database table to store translated labels, associated to item name and language. For example:

  create table item_labels (
      item_name varchar2(255)
    , item_label varchar2(4000) 
    , lang varchar2(10));

After that you have to create function for fetching label for specific item and current language (that can be set in some application item):

  create or replace function get_item_label (p_item_name varchar2
                                           , p_lang      varchar2 default 'EN')
    return varchar2
    v_item_label item_labels.item_label%type;
      select item_label
        into v_item_label
        from item_labels
       where upper(item_name) =  upper(p_item_name)
         and upper(lang)      = upper(p_lang);
     return v_item_label;
    when no_data_found then
      select pi.label
        into v_item_label
        from apex_application_page_items pi
       where pi.application_id = v('APP_ID')
         and pi.page_id = v('APP_PAGE_ID')
         and pi.item_name = p_item_name;
      return v_item_label;
  end get_item_label;

To dynamically change item label you have to modify definition of item label template to hide default label and to show one from database (using TRANSLATE_LABEL shortcut).

And the last step should be creation of shortcut (in my example named TRANSLATE_LABEL) that for source has PL/SQL function body that returns translated label:

At the end just add data to item_labels table and apply item label template. Don’t forget to create application item CURRENT_LANGUAGE that can be set dynamically.

For item label with help definition should look like this:


  1. Thank you for sharing this! This was exactly I was looking for.
    Could you give a tip how it works forlabels with help, i.e. how must I change the label template then?
    Thank's a lot,


  2. Hi Kirsten,

    It should be the same, with additional A tag with JS function popupFieldHelp for calling modal popup with help.

    If you had in mind translated modal dialog with help, you should create JS function for handling it.
    If you need help, I can write post about it.


  3. Hi Marko,

    thank you for rapid answer! I use theme 21 (APEX 4.1) and in the label template "Optional with help" in the "Before Label" section is written

    {label for="#CURRENT_ITEM_NAME#" tabindex="999"}{a class="optional-w-help" href="javascript:popupFieldHelp('#CURRENT_ITEM_ID#','&SESSION.')" tabindex="999"}

    The "After Label" section contains


    (please replace "{" and "}" by "<" and ">")

    If you have an idea how to change the template so that the translation function works as for labels without help it would be great.
    And I'm also looking for a similar solution for interactive report column headers.

    Best regards,

  4. Hi Kirsten,

    look at the end of updated post.

    For IR column headers you can use hidden items on page which you populate on page load or in default value property. After that use this items for column heading definition using syntax &PX_ITEM_NAME.


  5. Hi Marko,

    it works now for labels with help. I used the same structure as for labels without help and copied the a-tag into it.

    I hope for interactive report labels there exists a similar solution.