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 wierd 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

Eventually all APEX developers need to have multiple versions of the development environment open at the same time. A simple example of this is if you’re working with two pages (Page 1 and Page 2) that are dependant on one another. You constantly have to toggle between them as you can’t have two tabs open for development (one for P1 and one for P2).

Most people either open a different browser or open a new browser tab in Private or Incognito mode. Now Firefox allows you to get around this issue by having Multi-Account Containers.

Multi-Account Containers segregate your cookies by containers. Aside from APEX development you can also have multiple instances of Gmail or Office 365 opened in different container tabs.

For now you must install the Multi-Account Containers Plugin (built by Mozilla) as it’s not included by default. Note I use the plugin on Firefox Developer Edition with no issues.

I’ve written a few posts on how to Dockerize your Oracle and APEX setup (post 1, post 2). I’m constantly refining the process as I learn new things and different Docker images become available.

I was writing a third post on setting up multiple APEX environments using Docker and Oracle 12c. The post was getting long and included many steps. I decided to move the instructions to Github as it solves two problems:

• I can easily keep it up to date with the latest steps I do to create my Docker containers
• Others can contribute to the instructions (and/or include scripts) by making pull requests (PR)

To view my latest Docker setup you can view my docker-oracle-setup page on Github. If you have any corrections, suggestions, or enhancements please create issues or submit a PR on the repository.

If you want to convert decimal (base 10) to to hexadecimal (base 16) in SQL you can do so easily with the built in to_char functionality:

1
2
3
4
5

select trim(to_char(300, 'XXXXX')) dec2hex
from dual;
DEC2HEX
12C

What if you wanted to convert a decimal number to base 20 or any other base? This question recently came up when I was looking to add a transactional number generator for OOS Utils. The goal of the ticket is to convert a sequence number into a human readable number (ex: invoice number). It can easily be solved using PL/SQL (solution below) but I wanted to see if I could do it in SQL as it presents an interesting problem.

The Formula

For some, this section will go down memory lane to your college / university years where you had to convert dec to hex by hand. It’s important to understand the formula to convert dec to hex (or any base),

Using the first example, this is how to convert 300 to 12C “by hand”: Note Q = Quotient, R = Remainder

• 300/16 = Q: 18, R: 12 (hex: C)
• 18/16 = Q:1, R: 2 (hex: 2)
• 1/16 = Q: 0, R: 1 (hex: 1)

The steps above produce the hexadecimal number 12C. More importantly, each value is dependent on the calculation of the previous line. This isn’t easy to do in SQL.

PL/SQL Function

The following function will convert dec to hex in PL/SQL:

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

create or replace function dec2hex (
  p_num in integer)
  return varchar2
as
  l_return varchar2(255);
  l_quotient integer;
  l_remainder integer;
  c_base constant pls_integer := 16;
begin
  l_quotient := p_num;
  while l_quotient > 0 loop
    l_remainder := mod(l_quotient, c_base);
    l_quotient := trunc(l_quotient / c_base);
    -- A=10, B=11, ... F=15
    l_return := substr('0123456789ABCDEF', l_remainder+1, 1) || l_return;
  end loop;
  return l_return;
end dec2hex;
/

As you can see PL/SQL uses the quotient from the previous loop to calculate the current loop’s remainder.

SQL Solution

In SQL, finding the value of the previous row is easy using an analytic function such as lag. If you’ve never used analytic functions before, Connor McDonald has a great series on youtube about them.

Using the previous row’s calculated value for the current row is more difficult and can’t be done using analytic functions. This is where the model clause comes in. It’s one of the lesser known features in Oracle SQL but very powerful in the right circumstances.

The query below will do dec to hex (or any other “base X”) conversion. For demo purposes I have left the select ... from my_data portion uncommented to highlight the model clause. To do a full translation uncomment the last few lines.

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

var x number;
var base number;
:x := 300;
:base := 16;
with
  -- Find how many loops we'll need to convert dec to basex
  lvls as (
    select level lvl
    from dual
    -- The <= logic will return the number of characters required for conversion
    connect by level <= ceil(log(:base, :x)) + decode(log(:base, :x), ceil(log(:base, :x)), 1,0)
  ),
  -- Alphabet 0..Z
  -- Where 0-9, A=10, B=11 ....
  alphabet as (
    select
      level-1 num,
      case
        when level-1 < 10 then to_char(level-1)
        else chr( ascii('A')+level-1-10)
      end letter
    from dual
    connect by level <= :base
  ),
  -- Returns rows for all the Quotient and Remainder in dec value
  my_data as (
    select
      lvl,
      quotient,
      remainder
    from lvls
    model
      return all rows
      dimension by (lvl)
      measures( 0 remainder, 0 quotient)
      rules (
        -- Order matters here. I.e. R must come after Q so R can "see" Q
        -- cv docs: https://docs.oracle.com/database/122/SQLRF/Model-Functions.htm#SQLRF51210
        quotient[lvl] = trunc(nvl(quotient[cv(lvl)-1], :x) / :base),
        remainder[lvl] = mod(nvl(quotient[cv(lvl)-1], :x), :base)
      )
  )
-- For demo purposes
select md.*, a.letter
from my_data md, alphabet a
where 1=1
  and md.remainder = a.num
order by md.lvl
-- Uncomment below for dec2hex conversion
--select
--  listagg(a.letter, '') within group (order by md.lvl desc) basex
--from my_data md, alphabet a
--where 1=1
--  and md.remainder = a.num
;
LVL   QUOTIENT   REMAINDER LETTER
  1         18          12 C
  2          1           2 2
  3          0           1 1

Final Thoughts

The model clause is extremely powerful when used to solve problems it was intended for. The toughest thing to do is learn and understand how it works with all it’s options/function. If it’s any help, I’m still learning more about the model clause and by no means an expert.

If you do some interesting things with a model clause please blog about it and send me a message as I can list future posts in this article.