Recent Posts

An introduction into the APEX 5.1 Layout View

Von Tobias Arnhold → 4.04.2017
Many of you are still using the old "Component View" but the "Page Designer" introduced in APEX 5 made the developer life much easier.

The top 5 most time saving abilities for me are:
 1. Easy access on all page elements (without any page refresh)
 2. Copy&Paste of items, regions, dynamic actions, ... 
 3. Drag&Drop moving of items and regions
 4. Multi edit of items
 5. Since APEX 5.1: The ability to customize your own personal Page Designer View

The functionality "Layout" introduced in APEX 5 was not in my focus and as I remember I had some issues with it. So I just ignored it.

In APEX 5.1 I started a rerun on it using the "Universal Theme" and I must say it is a real help when you create a complex form page with a lot of page items. It feels different in APEX 5.1 and works almost flawless.

To get in touch with it I prepared a little example for you to help understanding the feature and may get inspired to use it, too.
We start with a page including 16 page items which should be visualized on 3 rows. Some of them are conditional and they are from different types (Text Field, Radio Group, Select List, Textarea).

All items are now displayed one below the other.

What do I aim for in the first step?
1. All items should have the label set to be "above". (Do not use the region template option for that)
2. I want to edit 8 page items split on two rows
3. Items 1-3 per row should be displayed over 2 columns
4. Item size must be adjusted

In between I made a Dynamic Action to make the "detail group" item conditional based on "Organization" if it is null or not.

I did the same for the category selector but I needed some Javascript code to make it right.

apex.item( "P9_CAT1_NAME_A" ).hide();
apex.item( "P9_CAT1_NAME_B" ).hide();
apex.item( "P9_CAT2_NAME" ).hide();
apex.item( "P9_CAT3_NAME" ).hide();
apex.item( "P9_CAT4_NAME_V1" ).hide(); 
apex.item( "P9_CAT4_NAME_V2" ).hide(); 
apex.item( "P9_CAT4_NAME_V3" ).hide();    

if ( $v("P9_CATEGORY_SELECTOR") == '1' ) {
    apex.item( "P9_CAT1_NAME_A" ).show();
    apex.item( "P9_CAT1_NAME_B" ).show();     
if ( $v("P9_CATEGORY_SELECTOR") == '2' ) {
    apex.item( "P9_CAT2_NAME" ).show();  

if ( $v("P9_CATEGORY_SELECTOR") == '3' ) {
    apex.item( "P9_CAT3_NAME" ).show();  
if ( $v("P9_CATEGORY_SELECTOR") == '4' ) {
    apex.item( "P9_CAT4_NAME_V1" ).show();
    apex.item( "P9_CAT4_NAME_V2" ).show();   
    apex.item( "P9_CAT4_NAME_V3" ).show();       

In my final move I finished the page by adjusting the other items.

The page looked like that:

But as always it could be a little bit better and luckily I used APEX. I just had to make a little change on it:

That's it. Hope you will give the "Layout"-View a chance to proof itself. :)

In case your Page Designer mentions problems in applying some multi update settings. Just refresh the page and try again.

Customize your Interactive Report with CSS

Von Tobias Arnhold → 3.28.2017
A lot of you are using jQuery to customize visual parts of an APEX application. I probably to often do so myself but there is a much more elegant way:   CSS

Nowadays you are able to add different kind of rules into your CSS styles. In this example I will show you how to get into the topic by changing an Interactive Report (IR). In my example I want to change the typical group by visualization.



First of all you need to give your IR a unique name or class to identify it properly.
IR > Appearance > CSS Classes: irCustomStyles

Now you need to add the customized CSS code in your page:
Page > CSS > Inline

.irCustomStyles .a-IRR-table td {
    border-top: 0 solid #ffffff !important;

.irCustomStyles table.a-IRR-table th:first-child.a-IRR-header:not(.a-IRR-header--group) {
  background-color: white !important;

.irCustomStyles table.a-IRR-table tr:has( .a-IRR-header ) {
    border-bottom: medium none white !important;
    border-top: medium none white !important;

1. Rule: Takes the border from the TD elements away. Standard CSS probably most of you have done like this before. 
2. Rule: This one changes only the first TH element and when they are not referenced with class ".a-IRR-header--group"
3. Rule: The "has"-clause only applies if the result is true. In this case if a class called "a-IRR-header" exists.

Fore more details check those sources:
:not(s) - By Sara Cope
Selecting Parent Elements with CSS and jQuery - by Thoriq Firdaus

Run dynamic action from report row and pass multiple variables

Von Tobias Arnhold → 2.23.2017
Execute a "Dynamic Action" by clicking on a button/link inside a report row is mostly handled by some triggering HTML class.

It actually works in 95% of all cases. But it is not the best way to do it. It is much more effective to execute the "Dynamic Action" with a custom event.

Reason is simple: You don't need to allocate unnecessary elements via a class by jQuery. You execute the "Dynamic Action" in the moment when it is needed. Like calling explicitly a Javascript function.
This matters if you show maybe 500 rows or more on a single page or you handle several dynamic actions in one report.

Running a dynamic action from inside a report is actually an old hat because there are a few guys which have been written about it. Anyway I'm still searching for it every time I need it and some of the code pieces are not up to date anymore. So I will show you the way I handle it today and probably tomorrow, too. :)

First I must thank Jeff Eberhard for his examples about this topic. He really inspired me using the technique in one of my projects where I had to suffer with many rows inside a report.

Blog posts (
Run Dynamic Action from JavaScript
Execute Dynamic Action From Report Column Link
Pass Multiple Values from Report to Dynamic Action

I prefer this way:

1. Set up a "Dynamic Action":
Custom Event: setIemsFromReport
Selection Type: Javascript Expression
Javascript Expression: document

1.1 Now add some action from type: "Execute Javascript Code"
In my example I set up three APEX items with data from my report row.

apex.item( "P1_DEVICE" ).setValue( );
apex.item( "P1_IP" ).setValue( );
apex.item( "P1_KOST" ).setValue( );

As you see the parameters are forwarded with specific variable names. The example came from Matt Nolan. I prefer it mostly because it is exact and it makes it easier to understand (maintainability).

1.2 To get the values into your APEX database session you add one more action from type: "Execute PL/SQL Code":

2. Report column
Inside my report I define a link column which looks like that:


javascript:apex.event.trigger(document, 'setIemsFromReport', [{device_name:'#NAME#', ip:'#IP#', kost:'#KOST#'}]);void(0);

apex.event.trigger executes the "Dynamic Action". 
[{...}] defines the variables to forward.
void(0) prevents the browser to do further actions.

Link Text:
<span class="fa fa-edit"></span> 

Link Attributes (not required):

By using some columns with case when clause I'm able to add some custom attributes like hide/show. Example:
    when SUBSTR(DEVICE,1,1) = 'T'
    then 'inline-block'
    else 'none' 

That is all you need to hand over attributes from your report row towards one or more APEX items on your page.

Using APEX_ERROR to manage custom error messages

Von Tobias Arnhold → 1.18.2017
Sometimes you just feel like you would be a newbie in coding business applications. Luckily it doesn't happen so often anymore. But this time it hit me hard. :)

During an application upgrade on Universal Theme I discovered an ugly workaround to create custom error messages I used in that time.

The old code looked like that:
  retval number;
  p_cust_id number;
  p_status varchar2(30);
  p_upduser varchar2(10);

  p_cust_id := :p1_cust_id;
  p_status  := 0;
  p_upduser := :app_user;

  retval := cust_apex_pkg.cust_apex_fnc ( p_cust_id, p_status, p_upduser );

  if retval = 1 then
    apex_application.g_print_success_message := '<span style="color: green;">Order was successfully published.</span>';
  elsif retval = 2 then
    apex_application.g_print_success_message := '<span style="color: red;">Error: Custom error 1 occurred.</span>';  
  elsif retval = 3 then
    apex_application.g_print_success_message := '<span style="color: red;">Error: Custom error 2 occurred.</span>';  
    apex_application.g_print_success_message := '<span style="color: red;">Error: Unknown error occurred.</span>';  
  end if;
As you can see I used the apex_application.g_print_success_message to overwrite the text color. Quite ugly but it worked as expected.
Anyway on Universal Theme it looked not so nice anymore because a "success message" has always a green background color. Putting some red text color above is not the user experience I would prefer.

I searched for about 3 minutes and found a really good article from Jorge Rimblas writing about the APEX_ERROR package. The blog post is from 2013 so this procedure must be available for a while now. What made me feeling like a jerk. The good side is that I now can start again to climb up on the iron throne. :)

The updated code looked like that:

  retval number;
  p_cust_id number;
  p_status varchar2(30);
  p_upduser varchar2(10);

  p_cust_id := :p1_cust_id;
  p_status  := 0;
  p_upduser := :app_user;

  retval := cust_apex_pkg.cust_apex_fnc ( p_cust_id, p_status, p_upduser );

  if retval = 1 then
    apex_application.g_print_success_message := 'Order was successfully published.';
  elsif retval = 2 then
      p_message => 'Error: Custom error 1 occurred.'
    , p_display_location => apex_error.c_inline_in_notification
  elsif retval = 3 then
      p_message => 'Error: Custom error 2 occurred.'
    , p_display_location => apex_error.c_inline_in_notification
      p_message => 'Error: Unknown error occurred.'
    , p_display_location => apex_error.c_inline_in_notification
  end if;


Thanks Jorges by blogging and sharing your knowledge.

APEX IR column only exportable for administrators

Von Tobias Arnhold → 1.13.2017
A few days ago I tweeted about a solution from Martin Giffy D'Souza to hide a specific column from export.

In my case I needed some username columns to be displayed during run-time but only exportable for administrators. The export itself was made with the default APEX CSV export.

Martin solutions was a really good starting point. I just had to add some security checks for the administrator role. And all together was put into a new authorization scheme which I added inside the conditional columns of my Interactive Report.

-- Authorization Scheme Name: ROLE_EXPORT_USERNAME
-- Validate authorization scheme: Once per page view

-- Code:
if pkg_security.has_user_role(:APP_USER,'ADMIN') = true or :REQUEST != 'CSV' then
  return true;
  return false;
end if;

A new year promises new possibilities!

Von Tobias Arnhold → 1.10.2017
The last year was quite successful even so it was a pain in the ass in many ways.
I have been on 3 conferences, made a few blog posts, got member at DOAG #NextGen, I initialized the "APEX Dashboard Competition" and I created a quite complicated example application which creates new solvable business cases for many of you: SVG in APEX.
This wouldn't be possible if I would have been alone. I worked with many different people in the community to get those activities done and I'm thankful for that.

Because I had the luck to talk to so many people I created a new idea regarding my main focus for this year "Getting students and trainees in touch with Oracle technology". This will not be just a simple presentation or some piece of code you create and publish. It is much more complicated and you can't do it alone.

By Kit from Pittsburgh, USA (Grads Absorb the News) [CC BY 2.0 (], via Wikimedia Commons

You may even have noticed it: Oracle will invest over 3 billion dollars for better education in the next 3 years. Only in Europe it will be around 1,4 billion. Something they may should have done 5 years ago. Anyway I'm curios if Oracle is just doing some great marketing slogan or if they start seeing the next generation of students as their most important invest after cloud.

In Germany we facing the problem finding new specialists since years. Except for a few companies we (the Oracle community) haven't done so much against it either.
Last year the German community woke up from their winter sleep by founding a new group inside DOAG called "DOAG #NextGen". This group focuses on students using Oracle products (Database, SQL, PL/SQL, APEX and more).

But why do we have the problem to find new students getting interested in Oracle software?
IMHO: Nowadays it is not enough to have a powerful engine and the fact that you can earn good money with it. It needs more to convince students to work with Oracle.

I tried it myself to get more students into Oracle APEX by making the APEX dashboard competition last year. Even so the competition was a success I have failed in my own focused main target: Getting new people interested into APEX.
I had do understand that it is much harder to get in touch with students then I thought. It is definitely not enough to post something in the most known IT event channels (Twitter, Eventbrite, XING, ...). It is actually the hardest and most time taking part to get in touch with them.

Luckily I don't give up so easily. At DOAG #NextGen we are planning a great event for students this year. More info's will follow.

I wish everyone a good start in 2017 and I will write about my next activities regarding students because I know that a lot of you understanding the need for Oracle specialists today, tomorrow and in 10 years.

SVG in APEX: Best Practices

Von Tobias Arnhold → 12.21.2016
In the last 8 month I was working on a new version of my SVG (Raphaël) example application. Of course I have a job (freelancer) and I have children. So the time to spend was diminished on only a few hours per week mostly on my way to work.
Some of the results were presented at the Swedish Oracle meetup in Summer '16. But the final application was made for DOAG 2016.

What leads me to an important point. To have the honor to present somewhere means for me also a passion to learn something new, create something new and share it with others to become a better developer. You may think 8 month of free time spend for a simple presentation is stupid but now I'm able to propose new business solutions to my customers which I didn't even thought were so easy to achieve.

And this is not all. One month before DOAG I was talking to a colleague "Sebastian Reinig" from another consulting company (Syntegris). A young men still studying for master grade and with a lot of passion towards Oracle APEX. He explained me that he worked on an APEX project using my former SVG solution from back in '14 and he enhanced my code by adding cool new features. (e.g. drag and drop in the harbor).
I mean those features were fitting perfectly in my example application. So I asked him to join my presentation at DOAG and show others what he was doing. An he did it. He added a new variant of my old example and helped me finishing the application.

That is why we should be presenting somewhere:
To become better by doing something new.
To become better by sharing your ideas with others and even get inspired by their ideas.

The result of this passion is the "SVG in APEX" application.

Example application URL:
User: demo
Password: demo


 1. Power grid (Great Britain including their main electricity pipelines)

2. Image tag editor

3. Harbor - Edit mask

4. Harbor - Drag and Drop

5. Mapael - Country visualization with edit mask

6. Mapael - Country visualization with canvas spark-line charts

7. Raphaël - Transform, integrate and interact with external SVG file
The application includes several slides describing the way how to transform an SVG file towards Raphaël.

8. SVG.js - Integrate and interact with external SVG files