Przemek Staniszewski - Oracle APEX & me

Oracle APEX 4.0 Cookbook

2011-04-14T14:24:00.001+02:00

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.

Restart...

2011-04-14T14:16:00.001+02:00

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.

Next month I’m getting married (bye, bye freedom :), but by the end of April 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!

Referencing substitution strings

2010-04-30T15:05:00.000+02:00

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).

jQuery calendar in all APEX datepickers

2010-04-29T12:05:00.000+02:00

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();
});
</script>

Accordion menu in APEX

2010-04-23T15:56:00.008+02:00

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:

DECLARE
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

BEGIN
-- 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)
LOOP
-- If user can see parent tab, we add it and create second loop
IF (y.AUTHORIZATION_SCHEME is null OR APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION(y.AUTHORIZATION_SCHEME)) THEN
-- 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;
ELSE
v_parent_tab := 1;
END IF;
-- 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)
LOOP
-- Authorization check
IF (x.AUTHORIZATION_SCHEME is null OR APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION(x.AUTHORIZATION_SCHEME)) THEN
-- 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
ELSE
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>';
END IF;
END IF;

END LOOP;
END IF;

END LOOP;
-- 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
END;

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:

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

2010-04-23T13:40:00.012+02:00

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');
</script>

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

Test

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' });
});
</script>

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

Common installation errors (APEX & Oracle XE)

2010-03-03T12:19:00.005+01:00

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:

ALTER SYSTEM SET SHARED_SERVERS = 20;

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.

Tłumaczenie kalendarza i wbudowanych komunikatów

2009-11-16T01:51:00.002+01:00

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: http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/global.htm#CHDJFHBD

Journeyman level

2009-11-15T23:58:00.003+01:00

I’ve just noticed that I reached Journeyman level on Oracle APEX forum. I know that is not big deal (there are guys with thousands of points) but it means a lot for me. Now I know that after a year since I’ve started my APEX journey, it bring me somewhere and I don’t want to return.

Thank you for points and wish me luck, because there is a lot to learn :)

APEX calendar and built-in strings translation

2009-11-15T23:03:00.003+01:00

If you develop your APEX applications in language other than English, you have probably faced problem of translation some APEX built-in strings.

To translate things like month and day names in calendar or error messages like “X errors occurred” during validation process or “Invalid login credentials” after login, you should do two things.

To change calendar names and other texts referring to your system or browser language, go to Shared Components -> Edit Globalization Attributes. Change Application Primary Language to the one you use and set Application Language Derived From to "Use Application Primary Language" (unless you need other option).

To translate built-in strings, go to Shared Components -> Text Messages and create message with name associated with string you want to translate. If you want to translate login error message, name your message as “INVALID_CREDENTIALS” and write substitution string in your language.

More information about translation your applications and full list of message names to translate you can find in APEX documentation: http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/global.htm#CHDJFHBD

Region appears after button clicked

2009-11-15T22:32:00.007+01:00

Sometimes it is very useful to show report on developed page after some button have been clicked. It will allow page user to access it much faster and decrease amount of processing queries.

Let’s say we have a page with filter and report regions. In filter region we add some text fields and SHOW button. The report based on some SQL query with WHERE part referring to filter fields appears only when button SHOW is clicked.

To do it, go to developer mode and find on your page a branch associated with SHOW button. Go to its details and add request that will be send after submit (e.g. APPLY). Save changes.

Now go to your report region details and find Conditions segment. Select “PL/SQL Expression” as condition type and enter following code:

:REQUEST = 'APPLY'
OR instr(:REQUEST, 'fsp_sort') > 0
OR instr(:REQUEST, 'pg_R') > 0

Save your report. Now when you enter this page, the report region won’t show. If you click SHOW button, the page will submit and send request APPLY. As APPLY is condition to show report region, it will appear.

The other two conditions are needed to prevent disappearing of report region when you want to sort it or use pagination.

Case static fields

2009-10-28T17:36:00.005+01:00

At the beginning let’s do something easy but quite useful if you want to force user to use upper or lower case letters in text field.

We can achieve it in two ways – by using javascript or CSS. I prefer second method because it's more “pure” in my opinion.

Create text item (P1_TEXT) in your page. Go to item details and find HTML Form Element Attributes. Write there new style or add following attribute to existing one:

style="text-transform: uppercase;"

Of course instead of “uppercase”, you can use “lowercase” option. Accept changes and the only letters you can enter in P1_TEXT are upper.

If you want to use javascript you should add to the same HTML Form Element Attributes field, following JS code:

onKeyUp=”$x(‘P1_TEXT’).value=$x(‘P1_TEXT’).value.toUpperCase();”

onKeyUp=”this.value=this.value.toUpperCase();”

Apply changes and now every time you click some key on P1_TEXT, its value will be changed dynamically to upper case.

Let's start! / Zaczynamy!

2009-10-28T17:10:00.009+01:00

Let's start!

After almost one year I've finally decided to start my blog.
I’m going to post here some interesting problems I faced during my work with Oracle APEX in Pretius Company and more detailed answers to questions addressed on Oracle APEX Forum.
This blog will be written in English, but if you have any questions, feel free to contact me using Polish and German as well.
Keep your fingers crossed!

Zaczynamy! (Polish version)

Witam serdecznie na moim blogu poświęconym technologii Oracle Application Express (APEX).

Zdecydowałem się zacząć redagować tą stronę po ponad roku nauki ww. systemu. Pozwala on na szybkie i bezpieczne tworzenie aplikacji zarządzanych przez przeglądarkę internetową, stanowiąc ciekawą alternatywę dla PHP, J2EE i ASP.

Będę zamieszczał tutaj rozwiązania interesujących problemów z którymi zetknąłem się podczas swojej pracy w firmie Pretius oraz na oficjalnym forum Oracle APEX.

Ze względu na oficjalny język tego forum oraz niską popularność APEXa w Polsce, zdecydowałem się pisać bloga w języku angielskim, choć nie wykluczam także postów dwujęzycznych. Jeżeli jednak masz jakieś pytanie, bądź chciałbyś się dowiedzieć więcej o technologii i możliwości jej wdrożenia w Twojej firmie, pisz śmiało także w naszym ojczystym języku.

Coming soon...

2008-10-08T11:34:00.000+02:00

Here will be my blog :)