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
)
;

SQLcl has a great feature to easily export an APEX application.

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

> sqlcl giffy/[email protected]:32122/orclpdb1810.localdomain

SQLcl: Release 18.2 Production on Sat Jul 28 08:12:13 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Jul 28 2018 08:12:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> apex export 100
Exporting application 100  Completed at Sat Jul 28 08:13:14 EDT 2018

SQL>

This will produce a file in the current directory called f100.sql.

I was recently asked how to export an application using SQLcl and set the Build Status Override option. This is available when manually exporting the application as shown below. This option is usually set to Run Application Only in production environments to prevent others from modifying the application.

There is no option (that I know of) to set the Build Status Override option when doing an SQLcl export. After doing a diff between manual exports with the different settings for Build Status Override the only change is the parameter p_build_status=> 'RUN_ONLY':

If you want to create an application export for a run only environment (i.e. production) just run the following bash commands after the export:

1
2
3
4
5

APEX_APP_ID=100
sed -i.bu "s/wwv_flow_api.create_flow(/wwv_flow_api.create_flow(p_build_status=> 'RUN_ONLY',/g" f$APEX_APP_ID.sql

mv f"$APEX_APP_ID".sql.bu f"$APEX_APP_ID"_run_and_build.sql
mv f"$APEX_APP_ID".sql f"$APEX_APP_ID"_run_only.sql

This will produce two files:

1
2
3
4

> ls -1 f*.sql

f100_run_and_build.sql
f100_run_only.sql

Can you spot the bug in the following code? (Hint: it compiles and no run-time errors)

1
2
3
4
5
6

-- ...
l_json := apex_web_service.make_rest_request(
  p_url => 'http://someurl.com/data',
  p_http_method => 'GET'
);
-- ...

It’s not so obvious until I expose the code that preceded it. So let’s try this again (with more information). Can you spot the bug in the following code?

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

-- ...
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := 'Bearer token123';
l_json := apex_web_service.make_rest_request(
  p_url => 'http://a-differnt-url.com/data',
  p_http_method => 'GET'
);

l_json := apex_web_service.make_rest_request(
  p_url => 'http://someurl.com/data',
  p_http_method => 'GET'
);
-- ...

The issue with the second call to apex_web_service.make_rest_request is that it will use the request headers that were set for the first call (to a-differnt-url.com). If the second web service (someurl.com) doesn’t want/need/expect that header it can cause some unintended issues and may result in an invalid RESTful call.

To get around this simply clear the request headers each time before setting them / calling apex_web_service.make_rest_request. You can do this by calling apex_web_service.g_request_headers.delete(); The following code is the correct way to run the previous example:

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

-- ...
apex_web_service.g_request_headers.delete();
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := 'Bearer token123';
l_json := apex_web_service.make_rest_request(
  p_url => 'http://a-differnt-url.com/data',
  p_http_method => 'GET'
);

apex_web_service.g_request_headers.delete();
l_json := apex_web_service.make_rest_request(
  p_url => 'http://someurl.com/data',
  p_http_method => 'GET'
);
-- ...

Someone recently asked me about the difference between an APEX page item’s Source and Default attributes. I’ve always been confused about these options so I decided to further investigate them. This post will cover the differences between Source and Default along with some additional info.

Order of Operations

Page items can be set in different ways and there is an order of operations to which “setting” takes precedence:

1. Session State: What ever is in session state will always trump the page item’s Source or Default settings*
2. Source: Source comes next. For most people this is null or Database Column (if using Automatic Row Fetch)
3. Default: If the page item is still null then Default will be used. This attribute doesn’t have as many options as Source (just Static Value, PL/SQL Expression, and PL/SQL Function Body)

* The Source attribute has an option to ignore the current session state value.

Session State vs On Screen

Neither Source or Default saves the value to session state*. Instead it is what is shown on screen. If Source or Default is used the value will only be saved to session state once the page is submitted.

* Read below as the value is temporarily used in session state for the remaining duration of the page load.

Additional Info

Is Default always Necessary?

I think a lot of the confusion with Source and Default is that in some cases Default is redundant and unnecessary. I think Default is only required when the Source is set to Database Column, Item, or Preference. For the other options (see below) you can wrap the returned value with a nvl.

Source Used Attribute

Source has additional attribute that Default does not: Used

If the used option is set to Always, replacing any existing value in session state this means that after the page has loaded the value in Session State may have a value that is different than the value that is shown on the screen. The following image shows the result of this situation (i.e. different session state vs on screen value).

Temporary Change of Session State

Suppose that a Before Header computation exists and sets P1_DEMO to a static value: From Computation. This means that during page load, right after the computation is run, the session state value for P1_DEMO is From Computation.

P1_DEMO Source is set to From Source and Always, replacing any existing value in session state is used. After P1_DEMO is “loaded” during the page load computation, the session state of P1_DEMO is From Source for the duration of the page load. After the page is loaded the session state value is From Computation. The following image highlights this. For both the Before and After display only items the Source was set to &P1_DEMO.

This can cause some weird behaviors on a page that has AJAX calls. The value in session state changes during the page load process. After the page is loaded it may flip flop and the AJAX call could use the “old” session state value (see screen shot above).

One thing I really dislike when trying to install new command line (CLI) software is the uninstall process isn’t always very clear. Things can get worse if the software has additional dependencies (Java for example) especially when you need to have two versions of the same tool for different apps.

Docker solves this problem by keeping everything you need for a specific task in one container. The added bonus is that you’ll probably be able to find a Docker container for just about anything you want and it’s OS independent on Docker Hub.

At Insum’s 2018 Hackathon we developed skills for Amazon Alexa. To help the teams I published a Docker image that has both the AWS CLI and Alexa Skills Kit (ASK) CLI.

Most of the demos I’ve seen for containers that are used for CLI tools looked like this:

1
2
3
4

docker run -it \
  ...
  doccker_prefs
  my_tool.sh -some -parameters

Though these containers are helpful it’s not really useful for end users (i.e. developers) since the command can be tough to remember and the containers may exist after running the tool (i.e. you may need to manually delete them.

When building the Amazon ASK CLI container I wanted to avoid these issues to make it as easy as possible for developers to use. I did the following tricks:

alias the docker run command

Instead of having to remember the long docker run command with all the parameters I used an alias command to bundle it all together. Mine looked like this:

1
2
3
4
5

alias alexa="docker run -it --rm \
  -v ~/alexa-demo/ask-config:/home/node/.ask \
  -v ~/alexa-demo/aws-config:/home/node/.aws \
  -v ~/alexa-demo/app:/home/node/app \
  martindsouza/amazon-ask-cli:latest

A few key things to note from the above snippet:

• alias: This means that anytime I type in alexa in bash it will run everything in the quotes.
  • Windows users: alias doesn’t exist in DOS or PowerShell. I’m not aware of any alternatives. If you know of any easy ones please leave a comment and I’ll update this post
• --rm option: Deletes (self destruct) the container after the command is run. This means is that when I run alexa ask init (where ask init is the CLI tool) it’ll run the container, execute ask (with parameter init), then remove the container once ask is done.

Using the alias and --rm techniques you can easily have all the CLI tools you want without cluttering up your base machine. Of course there may be some minor performance impacts by Dockerizing them. Personally I didn’t notice any slowdowns and will start using this technique for all my future CLI tools.

A while ago I released a Docker image for ORDS using Tocmat. I’ve tagged that script and have since rebuilt this image to use ORDS standalone as well as updating it to 17.4.1.

If you’re interested in using it, the entire project along with documentation is available here: github.com/martindsouza/docker-ords.

A few things to note about this Docker image:

• The configuration folder points to a mountable volume. If you store your configuration on a shared folder or version control system you can have ORDS point at it. If you need to have multiple versions of ORDS running this can be helpful since they can all point to the same configuration.
  • If you don’t have a shared configuration that’s fine. The container will install and configure ORDS if one isn’t found.
• I could not post this image on Docker Hub as Oracle requires that you download ORDS yourself and accept their terms and conditions. This means that you need to build your own Docker image (this adds a few minimal steps to the overall process).
• The image currently does not have SSL support and can be added it in if there’s interest. Please vote or leave a comment on the “Add Support for SSL” issue if you’d like it.

If you have any other feedback or suggestions please create an issue on the project page.

In most cases error messages are shown on an APEX page when the user submits a form and a validation fails. Sometimes (not often) you may need to manually control the validations rather than using the declarative validations section. To do this you can use the apex_error API. The following is an example of how to call the API.

1
2
3

apex_error.add_error(
  p_message => 'Custom error from PL/SQL',
  p_display_location => apex_error.c_inline_in_notification);

This will generate the following on the page:

apex_error.add_error triggers a rollback and in most cases this is a good thing. In some very specific cases you may not want a rollback to occur. The rest of this article covers how to generate errors without a rollback occurring.

Edge Case

I recently had a rare edge case for a page submit and errors. The page was used to edit batch staging data. The requirements were:

• User submits page
• All the data is saved (in a staging table)
  • Since this is staging area, the data must be saved and committed each time regardless of errors
• Do validations against staged data
  • If errors were found, the page would show them (remember the data still needs to be saved)
  • If no errors were found, the staged data would be posted and then branch to another page.

The Solution

Given that apex_error.add_error performs a rollback the simple solution is to save the staged data, perform an explicit commit, and then do custom validations. Unfortunately this was not possible due to additional requirements that I left out of this article.

The following relies on the fact that you know about the apex.message API. If you do not please read this article.

This is an overview of the final solution:

• On Submit PL/SQL page process that:
  • Saved all the page’s data
  • Called a custom validation function that returned a JSON object. If no errors were found this function would return null. The results of this function were stored in the page item P1_ERR_JSON.
• Created an onPageLoad Dynamic Action to display errors using the apex.message API

The following pseudo code summarizes the above description:

Page Process

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

-- ...
-- Save staging data
-- ...
-- Validations:
l_err_json := null;

select sum(salary)
into l_salary_total
from my_stage_table;

if l_salary_total > 1000 then
  -- Produce JSON error like:
  -- {
  --   "errMsg": "Total salary must be less than $1000"
  -- }
  apex_json.initialize_clob_output;
  apex_json.open_object;
  apex_json.write('errMsg', 'Total salary must be less than $1000');
  apex_json.close_object;

  l_err_json := apex_json.get_clob_output;
  apex_json.free_output;
end if;

apex_util.set_session_state('P1_ERR_JSON', l_err_json, false);

onPageLoad Dynamic Action

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

err = apex.item('P1_ERR_JSON').getValue();

if (err.length > 0) {
  err = JSON.parse(err);

  apex.message.clearErrors();
  apex.message.showErrors([
    {
      type: apex.message.TYPE.ERROR,
      location: ["page"],
      message: err.errMsg,
      unsafe: false
    }
  ]);
}// end if

When an error occurred, the page looked like:

Wrapping Up

This solution is a summary of the final code. You can easily expand it to handle multiple error messages, associate with page items, etc.

Ever wonder how APEX generates error messages (shown below)? Want to create your own? Starting in APEX 5.1 you can create your own error and success messages using the apex.message library.

The following demos show how to create custom error and success messages in JavaScript.

Error Messages

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

apex.message.clearErrors();

apex.message.showErrors([
  {
    type: apex.message.TYPE.ERROR,
    location: ["page", "inline"],
    pageItem: "P1_DEMO",
    message: "This field is required",
    unsafe: false
  },
  {
    type: apex.message.TYPE.ERROR,
    location: ["page"],
    message: "Page level error",
    unsafe: false
  }
]);

Success Message

1
2
3

apex.message.clearErrors();

apex.message.showPageSuccess( "It works!" );

Wrap up

For more examples and full documentation read the apex.message documentation.

The next post covers how to leverage the apex_message API to simulate an error in APEX without an error happening (it will also cover why you’d want to do this).