Sunday, 23 April 2017

AUSOUG APEX 5.1 webinar series

Oracle APEX 5.1 webinar series

Australia/New Zealand ORACLE APEX webinars

Great speakers from USA, EU, AU and NZ

I am pleased to announce there is a huge interest not only among the speakers but also from the audience for this years 2017 AUSOUG APEX webinar series.
There is plenty of time for you to join us, please check AUSOUG Calendar for more details. First session is starting 27/04/2017 - registration.

Some of the speakers already confirmed

  • Dimitri Gielis, APEX RnD, Belgium
  • Tyson Jouglet, Skillbuilders, USA  
  • Scott Wesley, Sage Computing, Australia
  • Roeland Van den Eynde, APEX RnD, Belgium
  • Niels de Bruijn, MT AG, Germany
  • Oliver Lemm, MT AG, Germany
  • Aljaz Mali, Abakus Plus, Slovenia
  • Menno Hoogendijk, Qualogy, Netherlands
  • .....more to come soon
in no particular order :)

More information on Hope to see you there.
Happy APEXing,

Monday, 10 April 2017

APEX 5.1 migration - compatibility tips

APEX 5.1 migration

Home breadcrumb substitution values

Compatibility mode issues

The day has come to upgrade your system to APEX 5.1. This is a short post about things we encountered along the way.

Please note that I will be updating it as things pop up so watch the space.

1. First on the menu:

Ajax JS calls causing:
Ajax call returned server error ORA-20987: APEX - Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum. If you are unsure what caused this error, please contact the application administrator for assistance. - Contact your application administrator. for .

If you dig into debug mode you would get some more details where APEX was listing what page item this problem occurred for. Bottom of the problem was some page items had Security attribute Session State Protection set to Check sum required - Session Level and were used in AJAX calls which now cause security errors to be shown. Solution of the problem was to change this as:

2. problem had to do with Old Custom Theme template and application that was running in 4.x compatibility mode.

Apex 4.x compatibility apps have an issue with the home breadcrumb not substituting values rendered URL looks like this:

How to fix this? One way is to chance this breadcrumb template from:
To this:
There are other ways how to do call this JS fix but for clarity of the blog I used "After Last" section.
3. problem - Dialogs for applications running in compatibility mode 4.x version

Front dialog not at correct z-index – unable to click on it due to modal click blocker being in front. Selenium tests still worked fine.
How to fix this? Add CSS style to your template
body .ui-dialog.ui-front{z-index:1001 !important} 

4. problem - Interactive report Filter Issue for 4.x compatibility mode
When you select Action-> Filter, Chrome console throws this error and it does not display filter options dialog screen:

First hint was this was related to old Classic Date Picker functionality that has been decommissioned.

This was confusing as on this IR there was no usage of Classic Date Picker or anything like that nor did the page contain any page items that were Date Pickers. Plus some IR of the same application were working perfectly fine while few others failed. This indicated that APEX 5.1 LS libraries were not the cause of the problem otherwise it would not have worked at all, right?

Solution: After significant amount of hours investigating, what narrowed it down was a desperate page export and comparison between working and non working IR page.

Core cause of all problems had nothing to do with Classic Date Picker nor with any JQuery conflict. It came down to bad programming practice and simple region Static ID set by developers. Pages where Filter action had an issue had ID set something similar to:

I guess you see the problem straight away. What this invalid ID did it eventually broke JS functionality of IR as it was trying to use ID to handle user interactions. Once we updated IDs to include no spaces and less characters things were back to normal. All IR Filter actions started working again.

Hard lesson learned here. Only hint to APEX would be to add a check to static ID that it has to be a valid HTML ID for DOM element. Please note that same IDs in APEX worked perfectly fine.

5. problem - Minor CSS class/DOM changes between APEX 5 and APEX 5.1
This caused some testing scripts to fail. Once you inspect the DOM you would notice small DOM changes and all you have to do is update your test scripts.

This is all for now. Over and out.

Saturday, 1 April 2017

Oracle APEX 5 - Report template tip

APEX 5 and custom report templates

APEX Reports on a next level

Fiddly default setting - "Sortable"

Small things make life happy! but sometimes it can take 4 eyes looking for obvious and cost you hours of your time before you found a solution. Does it sound familiar?

First thing that comes to my mind when someone mentions Custom report templates in Oracle APEX is the name of fellow APEXer - Jorge Rimblas. Why?

So many times Jorge showcased how great your reports can be if you put minimal efforts into them. Staring from very simple examples then spicing it up and bringing it to whole new level of presentation and yet still keeping it simple which is the most important thing.

This blog is not about how to create a custom report template but to showcase one small settings that you need to be aware of to save yourself some time.

Please by all means I highly recommend reading through and checking Jorge's blog for any further tips how to create your first report templates.

Situation: we created a template looking like this that is now able to run from a query.
Excellent work give your self a pat! :D

Next step is you try to add another field to the report and then things go bad. What I mean by it?

Check out this example:

I have a report with set of nicely looking blocks that contain some formatted text. All beautified and made fancy by CSS.

Idea is to present them in order 1 to n.

What happened as soon as I would add new column to my SQL query - my report elements would be sorted by something other than what I planned for.
OK it was clear as a day that it was an issue with 'sort by' but what I could not pick up is why it was showing up when I am simply defining a new column.

Then thanks to the help of second pair of eyes and valuable APEX experience of Skillbuilders (thanks Tyson) I was pointed to report column property setting called
Of course as soon is was set to No things were back to normal. This thing is now clear as day but at that moment information simply was not getting to my brain.

That is why I decided to log it here so that next time I know what to look for.


Wednesday, 29 March 2017

How to preview your local image in HTML?

Web design tip

Preview an image from your PC live in your app

Nifty way to hack this problem - non APEX related

It is totally APEX unrelated but still can be used by developers regardless of technology.

Ever wanted to preview how your newly designed image will look like once you upload it to an app. I know what you will say why would I even want/need to do this now when we have apex-frontend-boost or by doing it directly in your app.

What if you do not have access to DEV/PROD environment or application server and need to deliver newly designed image for your client application? Or maybe you do not want to bother setting these up. Or just wanna do one-off thing.

If you google this more than once probably means you need to write it down, doesn't it. So doing the same for my future reference.

Situation: Create a logo for client Facebook account. You spent time to design a new logo and now it is time to try it before it gets deployed by your client.

How do we go about to test this?

Again google is your friend, I found this solution there too and I am sure all of them work in a similar way.

If you inspect your FB page you are looking for section containing "_qa1" class this is where normally FB logo sits:
<div class="_qa1"><a aria-label="Profile picture" class="_2dgj" href="....." rel="theater"><img class="_4jhq img" src="..." alt="" width="170" height="170"></a><!-- react-empty: 8 --></div>
  1. Open browser inspector tool. Please remember not to refresh the page at any point as changes will be lost.
  2. Find an image you want to temporary replace. Check if it has ID assigned if not assign one to it by changing its DOM HTML with ID = "your_ID".
  3. After the image add this code into your HTML:
    <input onchange="document.getElementById('your_ID').src = window.URL.createObjectURL(this.files[0])" type="file" >

What this will do is let you upload the file from your local PC. Simple. 

Once you show it to your client and you all are happy, it is deployment time.


Tuesday, 28 March 2017

APEX 5.1.1 patch release

APEX 5.1.1 patch released

Available on OTN for download

Time to upgrade again

New APEX patch 5.1.1 is available from OTN - download

List of patch set notes can be found here -> 5.1.1 Patch notes.

As you can see list is a really long one so we better start upgrading our 5.1 instances.

Wednesday, 8 March 2017

APEX 5.1 Interactive grid - Making columns readonly

APEX 5.1 Interactive grid

Making columns readonly

My second APEX 5.1 post

This is just a simple post on how to make some of the columns read only.

If you want to know in details how IG is structured and all those nice hidden little features please visit John Snyder blog

Again John did amazing job giving us tips from behind the scene even before APEX 5.1 was publicly available. Thanks John.

Situation: we created Interactive Grid for users to view/edit some table data. Now we realized some columns (other than primary key column) content is better not to be updated by end user. Further we want to allow them to enter new rows but disable them from updating the existing ones. How do we do this?

Check out this example:
We have a standard looking IG in editable mode. There are multiple ways how we can sort this one out.

Notice how rows except the first one are highlighted in gray. Showing it as "read only" and not accessible by end users.

First we can use Allowed Row Operations Column setting in Attributes section of your report where we define a column in our query that will dictate which rows can be updated or deleted.

But again this is now row based control.

What if we only want a particular column to be controlled? Then there is setting siting under your column properties Query Only that can preserve the value and make it inaccessible for Insert or Update operation.

Using this setting you can make it read only but trouble is then it is read only in all modes. Even when users try to enter a new row which is not what we want.
To achieve read only per column needed I used these steps. Assign an class "is-readonly" to your desired column in my example this is Department name.

Then add Dynamic Action Event: Mouse enter with as selector.
Executing Javascript code:
$( "" ).removeClass( 'is-readonly' );

Live demo

18/04/2017 Update 
Easiest  how to do this is by setting Column Properties as 


Thursday, 2 March 2017

APEX 5.1 - Interactive grid limitation

APEX 5.1 IG limitation

Deleting long text columns producing an error

Error: Request Entity Too Large

This is a quick post on thing I noticed on APEX 5.1 IG last night that I wanted to share.

It is highly recommended to have a user friendly error message handler on your application so that users do not get a nasty ORA messages but also importantly that malicious code does not expose any details it does not need to see.

On top of this a good practice is to keep track of all error messages that arise in your app which I have done using submit_feedback procedure.

This is exactly where my problem showed up.

I detected an error on a page so I went in to see my feedback records using internal APEX feedback UI -> Team development -> Feedback link.

All feedback were listed here as expected. I immediately notices how interface for this site has changed comparing to 5.0 as this used to be interactive report. I said great perfect example where to use IG. After a while I got more and more records in and after all issues were cleanup I decided why don't I delete some rows from my feedback list.

This is where this error showed up:

To expose all components of my error handling function that was posting feedback, all I was using was a proven method of:

apex_util.submit_feedback (
p_comment => '[message]= ' || l_reference_id|| ' - ' || p_error.message||chr(10)||'[additional_info]=' || p_error.additional_info||chr(10)||'[display_location]=' || p_error.display_location||chr(10)||'[association_type]=' || p_error.association_type||chr(10)||'[page_item_name]=' || p_error.page_item_name||chr(10)|| '[region_id]=' || p_error.region_id||chr(10)||'[column_alias]=' || p_error.column_alias||chr(10)||'[row_num]=' || p_error.row_num||chr(10)||'[error_backtrace]=' || p_error.error_backtrace||chr(10)||'[component_type]=' || p_error.component.type||chr(10)||'[component_id]=' ||||chr(10)||'[component_name]=' ||, p_type => 3, p_application_id => v('APP_ID'), p_page_id => v('APP_PAGE_ID'), p_email => null);
Seems like APEX is comfortable with saving this value but when it comes to deleting IG is hitting some sort of character limitation.

Please let me know if this sounds like something you encountered before. If really there is a limit in the buffer somewhere what does it mean and where does that sit currently. Last thing we want is for users to pick on this once application goes live, right?

I know there is a million other ways how I can store error log and review it. Simply I used this method before and it always worked so wanted to share my thoughts. I still find IG to be one of the best features of APEX 5.1.

Appreciate your feedback on this. Also to note that I am perfectly fine deleting 1 or 5 rows but all(19) seems to hit the limit.