31 January 2013

"Oracle APEX Best Practices" Review

I've read "Oracle APEX Best Practices" and I’m still surprised, why it hadn't been written earlier?!
The book covers in 6 chapters very powerful knowledge about developing applications using APEX in correct way. Starting with modeling of application and database, through development and deployment tips, ending with great advices about security and maintain.
If you have been using Oracle Application Express for more than several months, you should read this book and check if you use APEX in the proper way. If you are new to APEX, start with book for beginners, but keep in mind you have to learn best practices some day.

27 November 2012

Oracle APEX Best Practice

I've recieved a copy of new APEX book - "Oracle APEX Best Practice". 
6 chapters and almost 300 pages fully focused on best developing habits in Application Express look pretty interesting. After reading it, I'm going to publish short review of this book.

01 October 2012


After long brake, I’m coming back. I’ve been running several projects and had no time for usual blogging, but now I hope I’ll be able to share my experience with you keeping regular time.

I’m going to publish some post about advantage APEX charts and dashboards. I also would like to change blog layout and start running my demo application.

See you soon!

12 May 2012

Referencing substitution strings

Recently some people asked me why I use #IMAGE_PREFIX# substitution string instead of /i/ in my application when I refer to server located files. The reason is quite simple – it’s not always /i/. You can configure your server files path in Home>Application Builder>Application>Shared Components>Edit Application Definition by editing "Image prefix" option.

So it's better idea to use universal:
<script type="text/javascript" src="#IMAGE_PREFIX#jQuery/js/jquery-1.4.2.min.js"></script>

than hardcoded:
<script type="text/javascript" src="/i/jQuery/js/jquery-1.4.2.min.js"></script>

Remember also about difference between three referencing substitution strings:

  • #IMAGE_PREFIX# points to virtual path of server "images" directory (default /i/);
  • #APP_IMAGES# points to static files uploaded with APEX upload utility (so saved in database), that are related to specific application (and only this application can use them);
  • #WORKSPACE_IMAGES# points to static files uploaded with APEX upload utility (so saved in database), that are related to the workspace (any application can use them).

11 February 2012

jQuery calendar in all APEX datepickers

Would you like to have nice datepickers inside your application without any special coding or changing existing date fields?

If you use jQuery, just add this code to your page template HTML header and enjoy jQuery datepickers in every place where you have APEX datepicker item.

<script type=”text/javascript”>
$(document).ready(function () {
     $(".datepicker > input[id]").datepicker();

14 April 2011

Oracle APEX 4.0 Cookbook

Recently I’ve read several APEX books. There was a time when there was nothing but OTN forum and APEX instruction to read something about Application Express, but fortunately nowadays lots of good books are coming out. One of them is Oracle APEX 4.0 Cookbook written by Michel van Zoest and Marcel van der Plas.

As far as I know it’s first book 100% focused on version 4.0, what means if you think about starting APEX 4 development, it’s good point of start. Especially that the book looks like designed for someone who want to learn APEX 4 pretty fast and has experience with previous versions.
12 chapters are arranged from “Basic Application” to “APEX administration” and “Team Development”.  Authors raised some advantage subjects like Web Services, APEX API and creating Plug-ins. I like very much Chapter 10 – “APEX Environment”, which discuss very practical topics like version control, debugging and SQL Developer integration.

As the title suggest, chapters contain set of full recipes for selected issue. There are no multipage descriptions – each recipe leads you step by step to solve your problem. It makes the book very useful during daily routines at work, so you can use it just like manual or…cookbook (we have very similar document for new workers at Pretius).

I like this book for its simplicity and I wouldn’t hesitate to recommend it for someone who has started working with APEX 4 and had worked with previous versions.

23 April 2010

Accordion menu in APEX

Menu system built into Application Express is quite easy and very useful when you develop simple application. However there are situations when this template doesn't work or has limitations like:
  • when you have a lot of parent and standard tabs, you'll face problems with menu size;
  • if you want to use some page as a common detail level of reports related to different tabs, you must choice one tab to be highlighted as current;
  • to access submenu link, you must first load page, that is first in parent tab definition.
It would be nice to build more dynamic menu (like accordion) that solves above problems, but is still based on the same tabs system, so we don't need to create menu again in different way (using lists or hardcoded links).

The plan

To build accordion menu in APEX we need to do several things:
  • attach jQuery and jQuery UI libraries and styles to our template;
  • create dedicated place in our page template where we'll display menu;
  • create process that will read parent and standard tabs (you must have them!) from APEX views and generate menu code including security rules;
  • get it together and invoke jQuery accordion function.
The idea is to generate menu code before page is loaded, write the code to global variable and use jQuery to show menu based on its value.

jQuery integration

You can find jQuery and jQuery UI files on official website (www.jquery.com), where you can also customize its style. To integrate jQuery and APEX you must import downloaded files to the application or copy them directly on APEX server and refer them to in page HTML.

More information about integrating jQuery and APEX you can find in previous post (APEX and jQuery integration).

Menu building

First we must edit page template (Home>Application Builder>Application>Shared Components>Templates), to turn off standard menu, and create region to display accordion.
If you edit Two Level Tab page template, find #PARENT_TAB_CELLS# and # TAB_CELLS# strings and remove them. Simpler and faster is to use template without menu.
Then write <div id="menu"></div> in place where you want to see new menu.
You'll need to edit some HTML and CSS to get nice look and place menu region exactly where you want to see it.

If you don't have page zero, create it. Go there and create two hidden items (let's name them P0_MENU and P0_MENU_ACTIVE).

Go to Application Processes (Home>Application Builder>Application>Shared Components> Application Processes) and create new "On Load: Before header" (no conditions) process with following code:

v_result varchar2(32000); -- menu code
v_parent_tab number := 0;
v_active_parent_tab number; -- active parent tab
v_tabs_for_page number := 0;
-- session data
v_session_id NUMBER := v('SESSION'); -- session ID
v_application_id NUMBER := v('APP_ID'); -- application number
v_page_id NUMBER := v('APP_PAGE_ID'); -- current page

-- checking if current page has related tab
SELECT count(*) into v_tabs_for_page FROM apex_application_tabs WHERE application_id=v_application_id AND (tab_page = v_page_id OR instr(','||TAB_ALSO_CURRENT_FOR_PAGES||',',','||v_page_id||',')>0);
-- if so we generate menu
IF (v_tabs_for_page>0) THEN
-- In two loops we generate menu code
-- First loop - for each parent tab
FOR y IN (SELECT * FROM apex_application_parent_tabs WHERE application_id=v_application_id ORDER BY DISPLAY_SEQUENCE)
-- If user can see parent tab, we add it and create second loop
-- checking active parent tab
IF (v_parent_tab is not null and v_parent_tab>0) THEN 
v_result := v_result||'</ul></div>';
v_parent_tab := v_parent_tab + 1;
v_parent_tab := 1;
-- adding parent tab link to menu
v_result := v_result||'<h3><a href="#">'||y.tab_label||'</a></h3><div><ul>';
-- reating second loop of children tabs for current parent tab
FOR x IN (SELECT * FROM apex_application_tabs WHERE application_id=v_application_id AND TAB_SET = y.CURRENT_FOR_TABSET ORDER BY DISPLAY_SEQUENCE)
-- Authorization check
-- if tab is related to current page, we make its name bold, if not we just add its code
IF (x.tab_page = v_page_id OR instr(','||x.TAB_ALSO_CURRENT_FOR_PAGES||',',','||v_page_id||',')>0) THEN
v_result := v_result||'<li><B><a href="'||APEX_UTIL.PREPARE_URL('f?p='||v_application_id||':'||x.tab_page||':'||v_session_id)||'">'||x.tab_label||'</a></B></li>';
v_active_parent_tab := v_parent_tab; -- setting active parent tab
v_result := v_result||'<li><a href="'||APEX_UTIL.PREPARE_URL('f?p='||v_application_id||':'||x.tab_page||':'||v_session_id)||'">'||x.tab_label||'</a></li>';


-- Closing tabs list
v_result := v_result||'</ul></div>';
-- setting menu code to global variable
:P0_MENU := '<div id="accordion">'||v_result||'</div>';
:P0_MENU_ACTIVE := v_active_parent_tab-1;
END IF; -- if not we don't change menu

It'll generate and adapt menu, and then write its code to P0_MENU item every time you enter new page in your application.

Return to page template and in Header section write:

<script type="text/javascript">
$(document).ready(function () {
      autoHeight: false, 
      animated: 'fold', 
      active: Number($('#P0_MENU_ACTIVE').val())

Apply changes and run application. If your page uses template that you've just changed, you should see accordion menu filled with parent (as sections) and standard tabs (as links).

This method has been tested with APEX 3.2.1 and jQuery 1.4.2 (UI 1.8). It works with all browsers.

APEX and jQuery UI integration

We can import every javascript library (including jQuery) to Oracle APEX using standard import file process available from application level (Home>Application Builder>Application>Shared Components>Static Files) and then reference it in page header using #APP_IMAGES# or #WORKSPACE_IMAGES# as file source.

However files imported this way won’t be cached by browser (they are in database, not in server’s file system) and using this technique it’s hard to import packages containing several files referencing  to each other (like jQuery UI).

So if it’s possible you should upload jQuery and jQuery UI files directly on APEX server. To do this, find APEX folder and "images" catalogue inside. In "images" create subfolder "jQuery" and copy there jQuery and jQuery UI files.

Files from server

In "images" folder you should find imagelist.xml file. Edit it and add all dictionaries and all files you’ve just copied. Save the file, run SQL*Plus and execute script:

SQL> @apxldimg.sql PATH

where PATH is your APEX folder path. After several minutes all files will be available.

To use jQuery you must reference to imported files in HTML header (on single page or in page template) using:

<link rel="stylesheet"  type="text/css" href="#IMAGE_PREFIX#jQuery/css/ui-lightness/jquery-ui-1.8rc2.custom.css" />
<script type="text/javascript" src="#IMAGE_PREFIX#jQuery/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="#IMAGE_PREFIX#jQuery/js/jquery-ui-1.8rc2.custom.min.js"></script>

Files from external source

If you don't want or simply can't import jQuery libraries and CSS in this way you can load them from external source (it's good idea for OTN applications). Just put this text in HTML header:

<script src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
   google.load('jquery', '1.4.2');
   google.load('jqueryui', '1.8.1');

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/redmond/jquery-ui.css" />


To check if everything works fine, create new datepicker item on your page. In page HTML header put following code:

<script type=”text/javascript”>
$(document).ready(function () {
     $(".datepicker > input[id]").datepicker( { dateFormat: 'yy-mm-dd' });

After page refresh, when you click inside datepicker field, calendar region should appear.

03 March 2010

Common installation errors (APEX & Oracle XE)

Recently I’ve installed new instance of APEX with Oracle XE on computer connected to corporation network. During this process I experienced a few problems with installation and APEX performance.

I was unable to complete installation of Oracle XE without turning network adapter off. There were no error messages, but after installation I was unable to enter database homepage (404 error) and login to database using SQL*Plus. You can solve this problem by unplugging network or turning off proxy. There must be some problem with proxy or DNS settings.

Another (this time quite common) problem is APEX performance just after new installation. If you feel, that your APEX runs pretty slow, you should probably change SHARED_SERVERS parameter in your database.
Just run SQL*Plus, connect as database administrator and write:


This way you can increase number of connections available for HTTP requests  that render your page.

If you use IE, then good idea (especially if APEX with Firefox works much faster) is to check HTTP 1.1 settings in Tools > Internet options > Advanced.

16 November 2009

Tłumaczenie kalendarza i wbudowanych komunikatów

Zgodnie ze wcześniejszą deklaracją, niektóre wiadomości będą umieszczane w dwóch językach. Zwłaszcza te dotyczące tworzenia aplikacji wielojęzycznych.

Jeżeli tworzymy przy pomocy APEXa aplikację w języku innym niż język angielski, z pewnością napotkamy problem związany z komunikatami wyświetlanymi przez silnik APEXa, których nie da się bezpośrednio przetłumaczyć.

Dotyczy to głównie kalendarza oraz wiadomości o błędach pojawiających się po procesie walidacji (
“X errors occurred”) i po podaniu nieprawidłowego loginu lub hasła podczas logowania (“Invalid login credentials”).

Aby poradzić sobie z pierwszym problemem wystarczy zmienić dwie opcję w menu zarządzania językami.
Należy w tym calu przejść do Shared Components -> Edit Globalization Attributes. Następnie zmieniamy Application Primary Language np. na język polski i ustawiamy Application Language Derived From na "Use Application Primary Language" aby zawsze zachować wybrany język (chyba, że zależy nam na innej z dostępnych opcji – np. pobierania języka użytkownika z przeglądarki).

W celu przetłumaczenia wbudowanych komunikatów na język polski należy przejść do
Shared Components -> Text Messages i utworzyć nową wiadomość tekstową o nazwie nadpisującej już istniejącą w systemie. Przykładowo aby zmienić komunikat błędu logowania, musimy nazwać nową wiadomość “INVALID_CREDENTIALS” i napisać nowy tekst w dostępnym polu.

Więcej informacji o tłumaczeniu aplikacji oraz pełną listę wiadomości możliwych do podmienienia znajdziemy w dokumentacji APEXa: