Changing Default Item Required Error Message in APEX

In APEX, if a page item is a required value it is no longer necessary to create an explicit validation. Setting the item's option "Value Required" to Yes will make it a required field as shown below.

This is much quicker then manually creating Not Null validations for each required item as required in older versions of APEX.

If the page is submitted and the item is null, then the page will be reloaded with a validation error message stating that " must have some value." The default message can me changed by doing the following:

• Go to Shared Components > Text Messages (under Globalization)
• Create Text Message
• Name: APEX.PAGE_ITEM_IS_REQUIRED
• Text: #LABEL# can not be null
• Use what ever message you'd like to see.
• Note that the substitution string #LABEL# is used for the item's label.

When the page is now submitted and the item is null, the page will be reloaded with a validation error message stating that " can not be null". 

The above technique changes the default message for all items that have their Value Required option set to Yes (i.e. it's an application level setting).

Stopping Dynamic Actions using apex.da.cancelEvent

Update: Nick Buytaert wrote a follow-up article on the apex.da.resume function. I suggest reading it along with this article in case you need to resume a Dynamic Action (which is especially useful for plugins).

They're a few times when you may need to stop a Dynamic Action part way through the executing all its actions. Since individual actions don't have conditions associated with them there's no supported way to do this. Thankfully there's an undocumented JavaScript (JS) function called apex.da.cancelEvent. Here's an example of how to use it:

Suppose you have a page with two items, P1_X and P1_Y, and a button on P1. When the user clicks the button the Dynamic Action (DA) needs to set P1_X = X and P1_Y = Y. The DA setup will look like the following:

When you click the button P1_X and P1_Y are set to X and Y respectively. 

Now suppose that after P1_X is set you wanted to stop all other actions in the DA (in this case setting P1_Y). There's no way to declaratively do this. Instead you must use the apex.da.cancelEvent function. 

Add a new JavaScript "True Action" to the existing DA with sequence 15 (so its between the two other actions). In the code section use: apex.da.cancelEvent.call(this);  Be sure to uncheck the "Fire On Page Load" option. The updated DA will look like image below (the new action is highlighted).

If you refresh P1 and click the button associated to this DA only P1_X will be set. The third action in this DA will not be executed.

The above example probably isn't the best use case for leveraging apex.da.cancelEvent however it does highlight its functionality. It is useful if you create your own custom confirm notice or some other process that determines if the rest of the actions in a DA can occur.

Note: You'll notice that I used the additional call() function when calling apex.da.cancelEvent in the action. Calling it defines the value of this in the function call as it is required in the cancelEvent function. For more information please read the JS call() documentation.

Parsing Imperfect JSON

In JavaScript, each JSON attribute name should be wrapped in quotes for it to be parsed correctly. Sometimes you may receive a JSON string that isn't properly formed because of the missing quotes. You can still parse it using the technique described below.

To start, here's an example of malformed JSON using JSON.parse and jQuery's $.parseJSON. You'll notice that it doesn't parse the JSON string since it doesn't have its names wrapped with quotes.

//Using native JS: JSON.parse
//Similar result for jQuery $.parseJSON
var jsonStr = '{ename : "martin", sal: 100}';
var jsonObj = JSON.parse(jsonStr);

//Raises error: 
SyntaxError: JSON.parse: expected property name or '}'


//Correct JSON object (notice attribute names are quoted)
var jsonStr = '{"ename" : "martin", "sal": 100}';
var jsonObj = JSON.parse(jsonStr);
console.log (jsonObj);

//Returns JSON object
Object { ename="martin", sal=100}

To get around this issue you can use JavaSript's eval method. It's not pretty but it does the job and will probably save you a lot of time trying to correct your JSON object.

var jsonStr = '{ename : "martin", sal: 100}';
eval('jsonObj = ' + jsonStr + ';'); 
console.log(jsonObj);

//Returns JSON object
Object { ename="martin", sal=100}

I've found this technique really helpful when passing back JSON strings from APEX AJAX calls (via a Dynamic Action, Plugin, or custom AJAX).

Update: Please read John's comment below as he makes a good point that the data should be data that you control otherwise perfect area for a JS injection attack.

Derived Columns in APEX Standard Reports

A while back I wrote an article about a feature in APEX's standard reports called Derived Columns. Despite using APEX for a very long time I had never really noticed it or used it before. Someone asked for some more information on derived columns, so this is a follow up article with a bit more detail.

Derived columns are place holders for an additional column in the report. The column can reference any of the query's columns but has some limitations such as not declaratively supporting links or being about to compute a value. It's most useful when you need an additional column to be displayed in a specific HTML format.

In the past when I wanted an additional column for display purposes I would just add it in the query as "..., null my_additional_column ... " and then modify the column's HTML Expression value. Now I can just a derived column and modify its HTML Expression.

To add a derived column, go to the Report Attributes then click Add Derived Column on the right hand side as shown below:

After clicking the above link a new column will appear in the list of columns.

You'll need to edit the newly added derived column and add something in the Column Formatting > HTML Expression field for values to appear. Tip: to reference columns in the HTML Expression field use the #COLUMN_NAME# notation. 

2014 Speaking Engagements

Wow, 2013 went by in a blur. Lots of exciting things happened last year both personally and professionally and I really look forward to 2014! Speaking of which, I have finally confirmed talks for the first half of the year which I've included below:


Kickoff to Kscope - Oracle ACE Directors Panel
Seattle, Washington
Jan 9th 4-6pm - Sheraton Seattle Hotel - Jefferson Room

Bring your Oracle questions (in pretty much any category) and discuss them with myself and fellow panelists (and ACE Directors) Kellyn Pot'Vin, Tim Gorman, Tim Tow, and Cameron Lackpour. This session will be moderated by ODTUG president Monty Latiolais.

Following the panel there will be a Happy Hour networking event. Both the panel and happy our are free!


Oregon and Southern Washington Oracle User Group (OSWOUG)
Title: APEX and HTML 5
Seattle, Washington
Feb 19th 4:30pm - Oracle Office (411 108th Ave NE #900, Bellevue, WA, 98004)

As the title suggests, I'll be talking about some really cool HTML 5 features and how to integrate them in your APEX applications. Of course I'll stick around after to answer any of questions.

Note: The OSWOUG website will be updated soon with my complete abstract.


OGh APEX World
Title: TBC (waiting for final abstracts to be accepted)
Zeist, Netherlands
March 25th (exact location TBC)

This will be my first talk in Europe! The speaker list is already very impressive with several ACE Directors and people from the APEX development team.

I'm not able to comment on the talk that I'll be giving yet as I sent in several abstracts to the content committee and will find out which one is selected soon. Once it's finalized I'll update this post with the exact details.

The above link is to the Call for Papers site which is still open until January 10th. If you're in Europe and interested in presenting at APEX World, it's still not too late.

I also hope to go speed skating there as the Netherlands is a mecca for long track speed skating. Trying to talk Roel Hartman into a race jj ;-)


Calgary Oracle User Group (COUG)
Title: Instrumenting your PL/SQL and APEX Applications
Calgary, Alberta
April 17 8:30am - Suncor Energy Centre (West Tower). 150 6th Avenue SW. 17th floor – Room 17L

This talk will cover Logger, an open source logging tool that I've been actively working on, and how to integrate it with APEX. I'll be covering a lot of PL/SQL integration so if you're not an active APEX developer it'll still be relevant.


ODTUG Kscope 14
Title: Just Log It.
Title: APEX 5 (TBC)
Seattle, Washington
June 22-26 - Sheraton Seattle Hotel

It goes without saying the Kscope 14 is the premier Oracle and APEX conference to attend. Besides having excellent content there's a lot of great opportunities to network and meet the experts from around the world. If you've read an Oracle book before, odds are the author(s) will be at Kscope. Best part is everyone is very friendly and approachable.

I'll be giving two presentations. The first is on Logger, an open source logging tool that I've been actively working on, and how to integrate it with APEX. The other presentation will be on APEX 5. Seeing as it's not out yet I don't have a specific topic but I'll make sure it's interesting.


That covers the first half of 2014. I'm really looking forward to all the events and most importantly seeing and meeting everyone!

- Martin