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: