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.

Last week Gerald Venzl announced on his blog that Oracle XE 18c was available. You can download Oracle XE at oracle.com/xe.

Since it was announced Adrian Png created a Docker image and I’ve been working with him help ensure that the data files can be preserved even when the container is destroyed.

Using the new Oracle XE 18c Docker script you can now build your own Docker image and run your Oracle XE as a container. I recommend that anyone planning to use Oracle XE on their laptop look at using this Docker image rather installing locally or a VM (more on this in a future article).

In the future Oracle may release its own official Docker image for Oracle XE. I will update this post when such an image is available.

I finally caved in and started to write all my queries using ANSI format (thanks Vincent Morneau for encouraging me to change to ANSI). I recently came across a situation where I needed to join two tables then outer join the result with another table. I could easily do this using Oracle joins (with an inner select statement) but was never a fan of it and wanted to see if there was a “cleaner” way to do this using ANSI joins.

Before continuing it’s important to review some tables that are required for this demo:

• dept: 4 departments
• emp: 14 rows that contain 3 of the departments (i.e. one department has no employees)

When you do an outer join (as shown below), 15 rows are returned as one department isn’t referenced in the emp table.

1
2
3
4
5
6
7

select count(1)
from dept d, emp e
where d.deptno = e.deptno(+)
;

COUNT(1)
      15

For this demo, a new table is required called dept_country. This will be used to join with the emp table to show the country each employee lives in:

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

create table dept_country as
select 
  to_number(regexp_replace(column_value,'(.*)-(.*)', '\1')) deptno,
  regexp_replace(column_value,'(.*)-(.*)', '\2') country
from 
  apex_string.split('10-Can,20-USA,30-Ger,40-Esp',',') x
;

select *
from dept_country
;

DEPTNO COUNTRY
    10 Can
    20 USA
    30 Ger
    40 Esp

If I try to “join“ emp and dept_country without any special handling it will give me invalid data. The following query returns 14 rows when it should return 15:

Note for all the remaining examples the goal is to get 15 rows

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

select d.deptno, d.dname, e.ename, dc.country
from 
  dept d,
  emp e,
  dept_country dc
where 1=1
  and d.deptno = e.deptno(+)
  and e.deptno = dc.deptno
;

DEPTNO DNAME        ENAME    COUNTRY
    10 ACCOUNTING   KING     Can
    10 ACCOUNTING   CLARK    Can
    10 ACCOUNTING   MILLER   Can
    20 RESEARCH     JONES    USA
    20 RESEARCH     SCOTT    USA
    20 RESEARCH     FORD     USA
    20 RESEARCH     SMITH    USA
    20 RESEARCH     ADAMS    USA
    30 SALES        BLAKE    Ger
    30 SALES        ALLEN    Ger
    30 SALES        WARD     Ger
    30 SALES        MARTIN   Ger
    30 SALES        TURNER   Ger
    30 SALES        JAMES    Ger

-- 14 rows (invalid)

The correct way to write the query using Oracle join syntax is shown below. I’m not a fan of the “inner select“ technique as it takes more time to write and you need to list out all the columns that are required.

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

select d.deptno, d.dname, e.ename, e.country
from dept d,
  (
    select e.deptno, e.ename, dc.country
    from dept_country dc, emp e
    where 1=1
      and dc.deptno = e.deptno(+)
  ) e
where 1=1
  and d.deptno = e.deptno(+)
;

DEPTNO DNAME        ENAME    COUNTRY
    10 ACCOUNTING   KING     Can
    30 SALES        BLAKE    Ger
    10 ACCOUNTING   CLARK    Can
    20 RESEARCH     JONES    USA
    20 RESEARCH     SCOTT    USA
    20 RESEARCH     FORD     USA
    20 RESEARCH     SMITH    USA
    30 SALES        ALLEN    Ger
    30 SALES        WARD     Ger
    30 SALES        MARTIN   Ger
    30 SALES        TURNER   Ger
    20 RESEARCH     ADAMS    USA
    30 SALES        JAMES    Ger
    10 ACCOUNTING   MILLER   Can
    40 OPERATIONS

-- 15 rows (correct)
-- Note the extra row for OPERATIONS that has no employees

For the rest of this article I won’t be showing the results of each query as they are the same for the 14 vs 15 rows as they are the results are the same as the two previous examples.

Replicating the same situation using ANSI joins the following query is incorrect since the emp is outer joined but dept_country is inner joined, thus negating the outer join.

1
2
3
4
5
6
7
8
9

select d.deptno, d.dname, e.ename, dc.country
from dual
  join dept d on 1=1
  left join emp e on 1=1
    and e.deptno = d.deptno
  join dept_country dc on 1=1
    and e.deptno = dc.deptno
;
-- 14 rows (invalid)

ANSI allows for brackets around joins to explicitly specify inner and then outer joins as shown below. I like this syntax as it’s very clear to write, read, and for others to understand. It’s also better than the Oracle inner select format as we don’t need to list out all the columns in a separate select statement.

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

select d.deptno, d.dname, e.ename, dc.country
from dual
  join dept d on 1=1
  left join (
    dual
    join emp e on 1=1
    join dept_country dc on 1=1
      and e.deptno = dc.deptno
  ) on 1=1
    and e.deptno = d.deptno
;
-- 15 rows (correct)

Suppose you had a users table that was generated with the following script:

1
2
3
4
5

create table users(
  user_id number generated by default on null as identity,
  home_phone varchar2(100) null,
  work_phone varchar2(100) null
);

User’s must have either a work or home phone but not both. I’ve seen some constraints to represent this requirement written as follows:

1
2
3
4
5

alter table users add constraint users_ck1 check(
  1=2
  or (home_phone is not null and work_phone is null)
  or (home_phone is null and work_phone is not null)
);

users_ck1 works fine but is open developer error when creating or modifying. For example, suppose cell_phone was added to the table. The script to modify the constraint to include the new column would be:

1
2
3
4
5
6
7
8

alter table users drop constraint users_ck1;

alter table users add constraint users_ck1 check(
  1=2
  or (home_phone is not null and work_phone is null and cell_phone is null)
  or (home_phone is null and work_phone is not null and cell_phone is null)
  or (home_phone is null and work_phone is null and cell_phone is not null)
);

A different approach that is easier to write and maintain is to use the decode function to apply a number (0 or 1) to represent if the column has a value or not.

1
2
3
4
5
6
7
8
9
10

alter table users drop constraint users_ck1;

-- Note: could use nvl2 instead of decode as well
-- nvl2(home_phone, 1, 0) + ....
alter table users add constraint users_ck1 check(
  decode(home_phone, null, 0, 1) + 
  decode(work_phone, null, 0, 1)
  = 1
)
;

Updating the constraint to handle a new column is very simple. Using the previous example, the following snippet shows how easy it is to add cell_phone to the constraint:

1
2
3
4
5
6
7
8
9

alter table users drop constraint users_ck1;

alter table users add constraint users_ck1 check(
  decode(home_phone, null, 0, 1) + 
  decode(work_phone, null, 0, 1) +
  decode(cell_phone, null, 0, 1)
  = 1
)
;