A client posed a reasonable question; could we just click on a button to reset an Interactive Report? Paraphrasing the conversation, the genesis of this request was that in order to reset a report we need to click Actions > Reset > Apply. That’s three clicks for what I would like to accomplish with one – said the client.

The APEX interactiveReport widget does contain a private _reset method, I was able to extend the widget with a public “reset” call this way:

if (typeof $.apex.interactiveReport === "function") {
    // only extend when the IR code is present
    $.apex.interactiveReport.prototype.reset = function() {this._reset();};

The code above is best if specified globally for the whole application (when Page Loads). The “IF” with typeof is to make sure the IR widget is present in the page, when your page does not contain an IR, the widget code will not be there and this call will fail.

Once this is in place, a button can reset the IR with a call like this:


Demo: https://apex.oracle.com/pls/apex/f?p=46011:410

Tested on APEX 5.1, 18.1 & 18.2 (will likely work the same on APEX 5.0)

More Details

There are several other methods to accomplish this. Depending on what you want to accomplish, you can use the RIR or CIR keywords on the Clear Cache field of the APEX URL


However, this method may require you also specify the Report in question (via the REQUEST ).

Also, you could use the API call APEX_IR.RESET_REPORT however it gets cumbersome to specify the currently selected report_id or report_alias. Plus it requires a page submission and that’s not always ideal.

The JavaScript reset() method taps into the standard reset of the IR, in my testing, it was the most flexible and fastest. Your mileage may vary.

DISCLAIMER: Because this method uses a private method call, it may change in future versions of APEX.

You may be familiar with the handy Substitution String #TIMING# in Oracle Application Express (read more about TIMING and other Substitution Strings).
When you add it to the footer (or header) of a region you get to see how long it took to run.

It’s pretty nice, but you may not want to display that value all the time. It’s sure nice during development. So why not make it optional? Or toggle it on or off?

Use the following CSS:

.timing {display: none;}
body.grid-debug-on .timing {
  display: inline;
.legend {
 font-size: .8em;
 color: #767676;

Add this line on a region footer:


Then the “Show Layout Columns” button on the APEX Developer Toolbar can be used to toggle the visibility of your timing entry.

Maybe this gives you other creative ideas, share them in the comments.

Keep in mind, as Peter mentions on Twitter (https://twitter.com/PeterRaganitsch/status/961727699667517440)
The #TIMING# value only makes sense for report regions and it couldn’t measure AJAX process.

Looking at the TIMING Substitution String is not a replacement for the Debug information generated by APEX.

Tagged with:

APEX 5.1 introduced native functionality for warning the user when they attempt to navigate away from a page when the page contains unsaved changes. However, this feature doesn’t track items created via the APEX_ITEM API. Which brings me to the poor man’s Warn On Unsaved Changes.

Here’s the basic technique:

  1. Use a regular text item (we’ll call it “Pn_COUNT_CHANGES”) on the page to track changes using the native functionality. I default it to zero (so it can be incremented as a number).

  2. Add a Dynamic Action that listens for changes on your APEX_ITEM elements. When a change happens just increment the Pn_COUNT_CHANGES item as if it were a counter. The selector could be by id: input[id^=ITEM_NAME] (notice the ^ which means “Starts with”). Or the selector may be by name input[name="f01"] or select[name="f01"]

  3. Add some CSS to the page to hide the Pn_COUNT_CHANGES item. You don’t want it to be a hidden item because then it will not be considered as part of the page changes (plus it would need to be unprotected).

display: none;

And that’s it. Here is the demo app https://apex.oracle.com/pls/apex/f?p=46011:90

This is how the Dynamic Actions looks:

Here is the True Action:

Closing Words

I admit this is a complete hack. It would also be enough to change the tracking item from 0 to 1, there’s no need to increment the value. That said, I like the idea of incrementing the value and if you implement something more complex undoing changes and decreasing the counter (if you set it back to the original value there would be no warning).

Perhaps a more native approach and some would argue even elegant, would be to use apex.page.warnOnUnsavedChanges. This API receives two parameters: pMessage and pExtraIsChanged. pExtraIsChanged is a function that you control and inside you can code the logic to track your changes. But that’s a completely different blog post.

I often need to have a single item on a page (think a search item above a report for example). I would like this item’s label to still align to the right, but be flush with the left side of the report. Like so:

However, with the current CSS grid implementation of the Universal Theme, this type of layout is not possible in a declarative way. Depending on the screen size, you always end up with some extra space.

Here’s a little bit of CSS that I find myself using a lot. If your item is called P310_SEARCH you will want to add the _CONTAIER suffix:

#P310_SEARCH_CONTAINER .t-Form-labelContainer {
 width: auto;

It basically finds the div for the label and changes the with to whatever the grid wants (a fixed percent) to automatic. By making it automatic, the width will now be the length of the label.


Here’s the end result with the CSS

and without the extra CSS:

Have you been thinking about submitting an abstract for Kscope? The submission deadline for Kscope18 is fast approaching! Submissions close on January 5, 2018!

Some of you may still be struggling to think of some topics. I’ve asked Dietmar Aust (Kscope18 APEX Track Lead), and Helen Sanders (Kscope18 Database Track Lead) for help with some topic ideas that would get your creative juices flowing. Scroll down and browse through the topics that are sure to trigger some ideas.
Aside from these general ideas, it is always great to hear about real-world projects. How were they approached? What were the challenges and which lessons were learned? This will help others to use good judgment and avoid problems proactively.

Finally, I should add, the list below is by no means inclusive. There are many other topics you could cover.

Reading Suggestions

If you know what you want to talk about, then get some inspiration for crafting the perfect abstract form this excellent blog posts:
* http://www.odtug.com/p/bl/et/blogid=1&blogaid=497 
* http://spendolini.blogspot.com/2013/11/presentation-advice.html
* https://www.linkedin.com/pulse/20140630141618-4832037-how-to-get-your-conference-abstract-accepted
* http://www.pythian.com/blog/concrete-advice-for-abstract-writers/
* https://chadthompson.me/2012/12/how-to-submit-winning-presentations/



  • Code quality, test-driven development for PL/SQL
  • Infrastructure setup and performance
  • Good practices in using SQL & PL/SQL for your APEX apps, new features for developers in Oracle 12.2
  • Version control with APEX
  • Development tools for troubleshooting, test automation, documentation, performance optimization, deployment automation, etc.
  • Security; threats and how to prevent them
  • Security; how to detect that information was leaked and/or stolen?
  • Modularization and Reuse: How can we extend APEX using plugins and how can we reuse code most effectively?
  • Beautiful APEX; Using and tweaking the universal theme to create beautiful apps
  • APEX Interactive Grid; interesting use cases and leveraging the API

APEX New Release

  • What is new in APEX 5.2 and why does it make a difference?

APEX Integration

  • Consuming and exposing RESTful services
  • Integration with SAP, AWS, Azure and other systems
  • Using different programming languages and tools like Java or sqlcl to support your APEX app either during development and/or at runtime
  • Using Docker in with Oracle and APEX


  • How to manage my Javascript code in my APEX app? Where shall I put it, how to debug it and what does the lifecycle look like?
  • Integrating new Javascript frameworks by converting them into APEX Plugins
  • Debugging and performance optimization of Javascript code in my APEX app


Database Administration – Foundational

  • Database In Memory
  • Security
  • Optimization
  • Upgrading, New Releases, and Patching (including new features)
  • Multitenant and Virtualization
  • Disaster Recovery and Backup
  • Performance Monitoring, Diagnostics, and Tuning
  • Best Practices (indexing, using external tables, etc)
  • Enterprise Manager

Database Administration – Beyond the Basics

  • Python for the DBA
  • REST data services for the DBA
  • DBAs in the Cloud
  • Blockchain
  • Web Services
  • Open Source
  • Docker

Database Modeling

  • SQL Developer Data Modeler
  • Database Design
  • Documenting the Database

Database Development – Traditional, Cornerstone, Foundational

  • PL/SQL
  • SQL
  • SQL*Plus
  • Upgrades, new Release features for developers
  • Performance topics – developer related
  • Best Practices
  • Data loading – sqlldr
  • Analytic Functions, Pattern Matching
  • Regular Expressions
  • SQL Developer – what’s new, tips and tricks

Database Development – Beyond the Basics

  • SQlcl
  • Python for Developers
  • Methodologies, eg Agile, SCRUM
  • Version Control (Git, SVN, etc)
  • Beacon Technology and tracking
  • Blockchain
  • JSON
  • JAVA
  • REST services
  • Open Source
  • MySQL, PostgreSQL, NoSQL
  • PHP
  • node.js
  • Ruby on Rails
  • Developers in the Cloud

This little question on Twitter prompted me to record this brief video.

Some useful links:

Sublime Text for Oracle Apex Developers


I recently had this requirement for freezing the column and header on a Classic Report, just the way you can with Excel. The report had too many columns and once you scroll horizontally or vertically you would loose track of the information you’re seeing.

It turns out freezing the header is pretty straight forward and it’s even out of the box functionality on the Interactive Reports, but not for Classic Reports.
If you only need to freeze the headers, just head over to Marko Gorički’s blog “How to make any table header sticky” and be done with it.

However, freezing columns is a completely different technique from freezing a header, or it requires special markup. Ideally, I didn’t want to create a Custom Template (even though, you know, I’m a big fan).

After some discussions and good advice from my buddy Vincent Morneau, I began scouring the interwebs for a suitable solution.

This JSFiddle https://jsfiddle.net/RMarsh/bzuasLcz/3/ seemed very promising and easy to adapt to Oracle APEX Universal Theme markup.

It seemed to me that I had to adjust selectors. So table became table.t-Report-report, thead became table.t-Report-report thead, and so on. You get the idea.

Then, after a working prototype, I saw the opportunity to bundle everything in a nifty easy to use APEX Plugin. You can find the plugin in apex.world and Github https://github.com/rimblas/apex-freeze-col-headers. Maybe you will find it useful.

View Demo

Using it is dead simple:

  1. Create an “After Refresh” Dynamic Action on the Classic Report you want to “freeze”.
  2. For the True Action select “Insum Freeze Headers & Column”
  3. Make sure “Fire on Initialization” is Yes.
  4. Don’t specify an affected element; the plugin uses this.triggeringElement

As you may already know, APEX 5.1 implements native Warn Before Exit functionality. Basically, if the user makes changes on a page and tries to navigate away before they save their changes, they get a warning. Modal Pages also get this feature, with one caveat; the close “X” on the window does not warn.
As I understand, this is was the intended behavior. However, for some use cases, I disagree. For this reason, and after a client’s request, I came up with this solution.
On the Modal Page attributes, we specify a “beforeClose” function. Like so:

beforeClose: warnOnClose

Then, on the calling page (or globally) you want the following code:

function warnOnClose(event, ui){
  var apexiFrame = $("iframe").get(0).contentWindow.apex;
  var hasChange = apexiFrame.page.isChanged();

  if (hasChange) {
    // var lMessage = apex.lang.getMessage( "APEX.WARN_ON_UNSAVED_CHANGES" );
    var lMessage = "There are unsaved changes. Do you want to continue?";
    var ok = confirm(lMessage);
    if ( !ok ) {
  return hasChange;

Line 2 and 3 are the most important to this whole solution. Modal Dialogs run inside an iframe. As such, the calling page needs to take a peek inside the dialog and see if something was changed.
Line 2 gets a hold of the “apex” namespace for the dialog. Line 3 can then check is anything has changed within it.

Finally, line 10, prevents the close even from happening and maintains the dialog opened if they user opts to stay.

I should mention, I tried to use apex.message.confirm instead of the more rudimentary confirm, however because we’re already inside a modal, calling apex.message.confirm would invariably end up closing the modal I wanted to keep open. Perhaps, there’s a way to accomplish this, if you have any ideas, please share in the comments.

Modal Dialogs in APEX5 use the Modal Page Title attribute as the Title of the dialog.

Standard Dialog Title
Standard Dialog Title

Often, I wish this valuable area of the page could convey more information. So, I thought I could use a Substitution String in the page title, but this doesn’t work that well because the value cannot be changed dynamically. To truly make it dynamic you need to use JavaScript.

Thanks to the awesome blog post from John Snyders I was able to take this example:

$("body").on("dialogcreate", ".ui-dialog--apex", function(e) {
        .dialog("option", "hide", {effect: "slideUp", duration: 1000})
        .dialog("option", "show", {effect: "slideDown", duration: 1000});

That enhances the dialog to open and close with a sliding effect. Into this:

$("body").on("dialogcreate", ".ui-dialog--apex", function(e) {
        .dialog("option", "title", $v("P50_DIALOG_TITLE"));

When “dialogcreate” event fires, we override the dialog “title” option with the title we want.
A Dynamic Action, on Click of our link, will grab the text of the link (via this.triggeringElement.innerText and save it on P50_DIALOG_TITLE. Looks something like this:


The end result:

Dynamic Title
Dynamic Title


Now, I was wondering if I could simply set the attribute via the Dialog Attributes, and you can! Notice that the Dialog is a different page, but the attribute is from an item on the calling page. This means that if you call a dialog from many pages you may want to use a JavaScript variable with the same name.


Finally, there is one more use case to consider. What if you navigate within the dialog to a different record, for example with Next and Previous buttons? In this case, this code may come in handy:

apex.util.getTopApex().jQuery(".ui-dialog-content").dialog("option", "title", $v("P55_TITLE"))

I have something to confess, but it shouldn’t come as a surprise – I’m not a very good DBA.
… but I’m not supposed to be… I’m a developer! Sure, I can discuss configuration options and architecture, but don’t ask me to install anything.

This is why I need to say “THANK YOU!” to the Oracle Technology Network for their Pre-Built Developer VMs. By providing a Virtual Machine, that runs on VirtualBox (free), pre-configured with the latest and greatest technology, I can work, learn, and play freely. It’s an incredible benefit that I don’t take for granted.

Check out what others are thankful: Search #ThanksOTN.