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

I recently opened my freezer found this bag of frozen blueberries:

I was a bit surprised to say the least, especially since the bag has a “E-Z Tab” along with a “PULL TO OPEN” (all caps) instruction label.

What does this have to do with software development? Everything. The rest of this post compares the bag of blueberries with software development.

What happened?

The most obvious thing to ask is why is their such a huge tear in the bag? Clearly someone didn’t take a few seconds to look at the bag and see that it already had built-in resealable functionality. Instead they determined it faster to tear open the bag (which it wasn’t) and then spent the additional time to find an elastic and in an unsuccessful attempt (first image) seal it back up.

Bad Design Encourages Bad Design

I’ve encountered the “torn blueberry bag” in many systems I’ve worked on over the years (some which have been my doing). I noticed that they all share the same pattern. A bad/quick design was put in place and then everything was piled on top of it just making the problem worse. When I run into these issues I try to analyze them and see if/how we can improve them. Here’s my detailed analysis on the bag of blueberries:

1- The Tear

This was a horrible decision for various reasons. First the opening now is too big which leads to more problems such as spillage, and does not make it easy to re-seal (more of this later). The “man-made” hole is not straight. Taking a pair of scissors would have made it more uniform, thus easier to maintain in the long run.

2- The Elastic

Now that the bag has been (poorly) torn the solution to re-seal it was to use an elastic that clearly wasn’t properly put on. This lead to a few stray blueberries scattered in the freezer when I took the bag out.

The elastic is a key point in this solution. When shortcuts occur in software people tend to focus on the elastic and come to the following conclusions:

• The elastic wasn’t put on properly
• Since the elastic wasn’t put on properly, the freezer had a bunch of loose blueberries in it
• I spent time cleaning out the freezer to get the loose blueberries

It’s easy to blame the how the elastic was put on and the side effects it caused but it’s the wrong approach. Instead of focusing on issues right in front of you, step back and ask yourself if this was the root cause of the problem or a side affect of a bad design decision.

3- The Future

For the rest of the life of the bag I have to find a way to properly reseal it. Sometimes the hack elastic job will work, sometimes it won’t. I’ll probably still be dealing with stray blueberries in my freezer, thus adding to the mess. Thankfully I like blueberries and will go through the bag quickly. I won’t have to deal with the torn bag for too long but software usually lasts a very long time.

Recap

A post-implementation analysis would have brought up the following questions:

• Did tearing the bag open rather than spending a minute to see if there was a better way save time? No.
• Did tearing it open make the situation better? No.
• What gain was made quickly tearing the bag open? None.
• What additional side affects were caused because of it? Wasted time, spilled blueberries, and this blog post ;-).

The perceived gain is that someone accessed the blueberries quicker for their needs. The reality is they just made things harder and created a compounding problem.

Lessons Learned

• When working on software projects always remember that taking a some extra time to think things through and review options can save a lot of time
• Rushed decisions usually lead to bad designs
• When encountering these issues, focus on the root cause rather than the symptoms
• Bad designs lead to more bad designs