A lot of clients want to see directly on which environment they are working on. Most of the time we did this by placing some text in the header which said: “Development” in some bright red color or something else that fitted.

To make it even more visible, often clients are asking to change the full color of the Universal Theme for each environment, i.e. Green for Dev, Orange for Test, Red for Acc and Blue for Prod.

This can be done easily, but everytime you need to do a full install of your APEX Application, the theme is updated to your current theme on Dev. So you either have to change it on Dev, or on the environment you where on.

With the script below you can either install the theme with PL/SQL after your application install (if you install with an install.sql file) or add the code to your supporting objects and enjoy.

For the script to work you need the following:

  • A view which maps your database to the environment
  • For each possible environment, a theme style


l_theme_style_id apex_application_theme_styles.theme_style_id%type;

l_theme_number   apex_application_themes.theme_number%type;

l_workspace_id   apex_applications.workspace_id%type;

l_themename      varchar2(50);


cursor c_get_omgeving is

select case

when omgeving = ‘D’ then ‘THEME_DEV’

when omgeving = ‘T’ then ‘THEME_TST’

when omgeving = ‘A’ then ‘THEME_ACC’

else ‘THEME_PRD’

end theme_name

from view_omgeving;


cursor c_get_theme(b_app_id in apex_applications.application_id%type

,b_name   in varchar2) is

select s.theme_style_id, t.theme_number

from apex_application_theme_styles s

join apex_application_themes t on t.application_id = s.application_id

and t.theme_number = s.theme_number

where s.application_id = b_app_id

and upper(s.name) = b_name;



–Needed for install through pl/sql

apex_session.create_session(p_app_id   => 1000,

p_page_id  => 101,

p_username => ‘INSERT_SOME_USER’);


open c_get_omgeving;

fetch c_get_omgeving into l_themename;

close c_get_omgeving;


open c_get_theme(b_app_id => v(‘APP_ID’)

,b_name   => l_themename);

fetch c_get_theme  into  l_theme_style_id, l_theme_number;

close c_get_theme;


apex_theme.set_current_style(p_theme_number => l_theme_number,

p_id           => l_theme_style_id);




Unfortunately, we were unable to make the supporting objects install automatically, due to a false value in; nvl(wwv_flow_application_install.get_auto_install_sup_obj, true). If we figure out how we can work around / fix this I will let you know.