Prior to 12.2 validating dates and numbers was a bit of a pain as you had to write your own custom PL/SQL function. OOS-Utils has a quick solution to this issue and I recommend using oos_util_validation.is_number and oos_util_validation.is_date.

If you’re using Oracle 12.2 or above there’s you can (and should) use validation_conversion instead. Here are some examples of how to use it along with results:

1
2
3
4
5
6
7
8
9
10
11

select
  validate_conversion('123.34' as number, '999.00') valid_number,
  validate_conversion('abc' as number) invalid_number,
  validate_conversion('01-Jan-19' as date, 'DD-MON-YYYY') valid_date,
  validate_conversion('01-BAD-19' as date, 'DD-MON-YYYY') invalid_date
from dual
;

VALID_NUMBER INVALID_NUMBER VALID_DATE INVALID_DATE
------------ -------------- ---------- ------------
           1              0          1            0

validate_conversion will return 1 for valid conversions and 0 for invalid conversions.

Note: in the next release of OOS Utils, the validation functions will use validate_conversion if your database is 12.2 or older internally.

APEX has the ability to easily have Read Only (RO) pages. The screen shot below shows how to define the page’s RO attribute. For this demo it’s set to Always but it could use any of the usual conditions in APEX.

When the page is loaded, all its page items are not editable:

They’re a few things to consider when using the Read Only attribute for a page:

Hacking a page item with JavaScript

In the previous image I can not modify the page item by standard means. I can still modify it using JavaScript (JS): apex.item('P2_NAME').setValue('martin'); Submitting the page will trigger an error (which is good):

This information is relevant as you should probably disable any Dynamic Actions (DA) that modify page items when the page is in Read Only mode. Conditionally running DAs can easily be done by applying the declarative condition to a DA Page/Region is Read Only. You could also use the TODO API if you have additional server-side conditions for a DA.

Page can still be submitted

In this demo, a page process has been created that logs a message using Logger.

If a user clicks the Save button the process is still run, despite the page being in Read Only mode. The next logical thing to do is to put a condition on the Save button so it does not appear when the page is in Read Only mode. Users can bypass this by running apex.page.submit('SAVE'); in the console. The following demo highlights these concerns:

Similar to the previous section, if your page uses the Read Only attribute you should also apply conditions on the corresponding processes to restrict them when the page is in Read Only mode.

Suppose you have a region with a set of cascading LOVs and report that references both of these items. When either of these items is refreshed, the report should be refreshed. When the parent item is changed it will trigger multiple refreshes of the report. The following example highlights this problem:

p2_deptno:

1
2
3

select d.dname d, d.deptno r
from dept d
order by dname

p2_empno:

1
2
3
4
5

select e.ename d, e.empno r
from emp e
where 1=1
  and e.deptno = nvl(:p2_deptno, e.deptno)
order by e.ename

Cascading LOV Parent Item(s): P2_DEPTNO

Report Region

Type: Classic Report

SQL Query:

1
2
3
4
5

select empno, ename, deptno
from emp e
where 1=1
  and e.deptno = nvl(:p2_deptno, e.deptno)
  and e.empno = nvl(:p2_empno, e.empno)

Page Items to Submit: P2_DEPTNO,P2_EMPNO

Next, create a Dynamic Action (DA) that will trigger a refresh on the Report region each time any item has changed:

When p2_deptno is changed, 3 Ajax requests occur:

1. Report region is refreshed (reason: Page items to Submit contains P2_DEPTNO)
2. p2_empno LOV is refreshed (reason: Cascading LOV Parent Item(s): P2_DEPTNO). This triggers another refresh to the Report region (see next step)
3. Report region is refreshed (reason: Page items to Submit contains P2_EMPNO)

The following demo highlights this problem. The report refreshes so quickly the only way to see the problem is to look at the differnet Ajax calls. Note: the order of the Ajax responses may change since it’s asynchronous.

If your report runs relatively quick you may not even notice the duplicate refresh. I had a situation where we had five cascading LOVs and the underlying report was refreshed five times and was very noticeable to the users.

Thankfully there’s a simple fix to this problem. When a JavaScript (JS) change event occurs and is triggered “by a user” the JS event e.originalEvent contains a value. In the above example step 3 was not really triggered by a user, rather triggered by a cascading LOV. In this case the JS event attribute originalEvent is null.

Using the event attribute originalEvent we can solve the problem by adding the following Client-side Condition to the DA: this.browserEvent.originalEvent !== undefined

Now when p2_deptno is modified only two Ajax calls occur (which is correct):

Thanks to Adrian Png for helping me with this.

While developing standard reports in APEX I usually do the following repetitive steps. Modify something to do with the report (query, setting, attributes, source data, etc). After saving or committing my changes I refresh the page to see how it looks. This gets frustrating when doing a lot of small edits as a full page refresh is not required.

I recently created a script to solve this problem. The script adds a small refresh icon in the first column header of each standard report. Clicking it will refresh the current report. The following demo shows it in action and how you don’t need a full page refresh to view changes to a report: (note: you don’t need to run this in the console, which will be discussed at the end of the post)

The following steps outline how to integrate this directly in your application:

• On Page 0, create a new Dynamic Action (DA)
  • When > Event: Page Load
    • Action > True > JavaScript:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

var buttonHtml = '<span aria-hidden="true" class="apex-dev-refresh fa fa-refresh fa-anim-spin"></span>';

$('.t-Report').each(function(){
  var $this = $(this);

  // Add button to screen
  $this.find('th:first').prepend(buttonHtml);

  // Regenerate the refresh button after a region refresh
  $('#' + $this.data('regionId')).on('apexafterrefresh',function(){
    $(this).find('th:first').prepend(buttonHtml);
  })

})

// Register event listener
$(document).on('click', ".apex-dev-refresh", function(){
  $(this).closest('.t-Report').trigger('apexrefresh');
});

The DA should look like this:

You can change the icon and its settings to anything you want (i.e. by modifying the buttonHtml). You can use the APEX Icon Generator tool to generate the code for a new icon.

Though not required, I highly recommend you add a Build Option (read Scott Wesley’s article - I have a Build Option called DEV_ONLY) to restrict the DA to development only.

You could add the code to a JavaScript file and include it with your application or create a Dynamic Action plugin. (If you create a plugin for this please let me know and I’ll be sure to refreence it.

They’re certain things in life that absolutely drive me crazy. I can probably start an entire blog on this topic but instead will focus on something I see many developers do in SQL Developer.

Unfortunately I’ve see a lot of developers run their queries using a mouse instead of a keyboard shortcut (note no semicolon between queries) as demonstrated below:

You’ll notice that my second query had an error with it so I had to select it again, then click the run button to execute it.

At first glance this may not seem like a big deal however if you run hundreds of queries a day that are much longer than two lines the inefficiencies really start to add up. With the use of the right syntax you can execute everything in SQL Developer via keyboard shortcuts. In the example below everything is run using ctrl+enter

The syntax structure that you need to use is very simple:

• After every query have a semicolon (;)
• After every PL/SQL block end with a semicolon (;) and then a forward slash (/)

The following code sample is what was used in the second demo:

1
2
3
4
5
6
7
8
9
10
11
12

select *
from emp
;

select *
from dept
;

begin
  dbms_output.put_line('hello');
end;
/

All my releases are done via scripts. In other words I don’t deploy APEX applications from the browser, rather straight from SQLcl. Since some of my releases can take a while and puts the code in an unstable state I usually disable the application for the duration of the release. This article will show how to disable an APEX application in PL/SQL.

You can manually disable an application via Shared Components > Application Definition > Availability > Status:

When setting the Status to Unavailable it will look like this for users:

Note: they’re various status options which allow you to determine how to define the status message.

Going back to releases, the overall order things occur in are:

1. Disable APEX application
2. Run release (DDL, DML, etc)
3. Update APEX application (this will clear the disabled setting that was manually done in the first step.

PL/SQL code to disable an APEX application:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

declare
  l_app_id apex_applications.application_id%type := 102;
begin
  -- Pre APEX 18.x
  -- oos_util_apex.create_session(
  --   p_app_id => l_app_id,
  --   p_user_name => 'RELEASE'
  -- );

  -- APEX 18.x +
  apex_session.create_session (
    p_app_id => l_app_id,
    p_page_id => 1,
    p_username => 'RELEASE');

  apex_util.set_application_status(
    p_application_id => l_app_id,
    p_application_status => 'UNAVAILABLE',
    p_unavailable_value => 'Updating application');
  
  commit;
end;
/

If using a version lower than APEX 18.x you’ll need to install OOS Utils to create an APEX session in SQL.

Before running the above code you’ll need to enable the runtime API by going to Shared Components > Security Attributes > Database Session > Runtime API Usage and check Modify This Application.

Documention for apex_util.set_application_status.

Suppose you have a requirement that all saved Interactive Reports (IR) names must be lowercase. How can you enforce it? Using some JavaScript (JS) you can “intercept” the save and delete functions and either continue on with them or not. Note my uses case was very different than a naming standard but using it as an example as it’s very simple.

The following code snippet will intercept the save functionality (you can inject your business rules accordingly). It’s important to note that this code must be run in the page’s Function and Global Variable Declaration section (as part of the page’s properties). A better alternative is to load it via a JS file (hint: use APEX Nitro). It will not work if you try to run it as is in your browser’s console.

Warning: The following code is using undocumented features in the APEX IR JS library. Use at your discretion.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

$(document).ready(function() {

  $.widget("apex.interactiveReport", $.apex.interactiveReport, {
    
    // Saving a Default report
    _saveDefault: function () {
      console.log("_saveDefault");
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    },

    // Saving a Public or Private reports
    _save: function () {
      console.log('_save');
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    },

    // Deleting a report (user clicking the "X")
    _remove: function () {
      console.log('_remove');
      console.log(this);
      console.log(this._getId("report_name"));
      console.log('stopping');
      // return this._super();
    }
  });

});

I’ve commented out return this._super();. Re-enabling it will continue the function (save, remove, etc) as expected. In most cases you would wrap it in an if condition based on your requirements.

The following demo shows this code in action. Both the save and delete requests don’t complete as the JS code intercepts the requests and logs some information.

When reviewing a team’s release processes I commonly encounter developers spending a lot of time and concern over the order of their PL/SQL objects (i.e. views and packages). For example if pkg_a references view_b then the release looks like:

1
2
3
4
5

...
@pkg_a.pks
@pkg_a.pkb
@view_b.sql
...

At first glance this may not seem too bad but when working with larger applications or a high change rate of code (think initial development) this can take up a lot of wasted time. It can also get much more complicated when dealing with many packages and views to understand their dependencies to ensure that the code is compiled in the “correct order”. Thankfully you don’t need to worry about this with Oracle as there’s an API to compile all the invalid objects:

1
2
3
4

begin
 dbms_utility.compile_schema(schema => user, compile_all => false);
end;
/

For all of this to work effectively ensure that all your views are written as create or replace force view view_name note the keyword force. This will ensure that the view is compiled even if it’s invalid.

Using the dbms_utility.compile_schema procedure and leveraging the force in the create view statements, most of my releases look like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- views
@view_b.sql
...

-- packages
@pkg_a.pks
@pkg_a.pkb
...

-- Recompile invalid objects
begin
 dbms_utility.compile_schema(schema => user, compile_all => false);
end;
/

For very active development cycles (where a lot of the code is changing) I even have scripts to scrape both the packages and views folder and auto-inject all the file names into the release file. This way developers don’t need to worry about which packages or views were modified for a given release (nor their order/dependencies) as they will all be re-compiled.

By default Oracle does case sensitive sorting, which means that ascending order goes from A-Z then a-z. This is reflected in APEX when creating reports and sorting on them.

They’re various workarounds to enable case insensitive sorting. The simplest is to change the entire application by modifying the following setting in Shared Components > Globalization > Character Value Comparison to BINARY_CI:

Once set, all your reports will be sorted case insensitive. The first report now looks like this:

Thanks to Jorge Rimblas for showing me this!

Google Chrome has a lot of great DevTools. Here are 5 tools that may help with your APEX development.

Live Expressions

If you had a hidden APEX page item that you wanted to monitor you constantly had to go into the console and type in write out console.log(...) to view its value. This can be annoying if the value will change multiple times as you test the page. Live Expressions (introduced in Chrome 70) resolves this issue by being able to track output by updating the value every 250 milliseconds.

The nice thing is that Live Expressions are preserved when you refresh a page. It’s hard to tell in the example above but I refreshed the page at one point.

Command Menu

Chrome DevTools have a lot of settings and features that you may not know about, let alone know where to access. Thankfully it has the Command Menu which is like Spotlight in MacOS. Anywhere in the DevTools type Cmd+Shift+P (Windows users Ctrl+Shift+P) to enable them. These shortcut keys are the same in Visual Studio Code so developers that use VSC will be very familiar with the behavior.

In the last part of the above demo I typed in Cmd+P (Windows users Ctrl+P). This will open a file that the webpage loaded or that you have in your workspace (shown below)

Dark Mode

A long time ago I changed my editor to a dark theme. Since then a lot of applications (including APEX 19.1 and MacOS Mojave) support dark themes. Chrome DevTools now supports dark mode.

Persist CSS Changes

A lot of times when doing basic UI / CSS debugging you may inspect an element and modify its CSS properties. When you refresh the page you lose all your changes. This is especially frustrating if doing live-CSS edits with end users to do quick proof of concepts.

You can persist CSS changes using the Chrome Overrides feature. To enable this go to Sources > Overrides > + to add a folder. This can be any empty folder you want.

When you do this you’ll get the following warning message. Be sure to Allow Chrome to access the folder. You’ll only need to do this setup once.

Now when you modify a CSS property and refresh the page it’ll persist even after you reload the page:

If you want to quickly reset all the changes you’ve made just delete everything in your overrides folder.

Adding Breakpoints in Code

They’re various ways to add breakpoints to help you debug. Sometimes you know exactly where in you JS code you want to add breakpoints but don’t want to manage it via the UI. In this case you can add breakpoints directly in your code by adding the debugger; command.

Offline Fun

This won’t exactly help you with your APEX development, nor is it a DevTool feature. If you’re ever offline and really bored Chrome’s got you covered. Simply hit the spacebar when you see the dinosaur to start and use the up and down arrows to move it around.