One of the lesser-used features of APEX is using REST Data Sources to call non-Oracle web services to interact with data stored in another database. APEX has had web service integration for years now, but only in the last few releases has it been enhanced enough to be easy to use and extensible at the same time. Using REST Data Sources, developers can now assemble declarative components that can be easily referenced as the source of a report or in PL/SQL.

As cool as they are, REST Data Sources do take some getting used to. Some features are fairly obvious and easy to learn, while others are not.

Rather than walk through all of the specifics of REST Data Sources, I thought it would make more sense to do it in the context of a real integration - something that we can all relate to. Thus, I selected to use Jira for this for several reasons.

First of all, Jira has a massive API set that’s actually somewhat easy to understand and fairly well documented. This will make it easy for anyone who wants to expand on the work that I outline in my posts.

Second, Jira also has a “free forever” tier. You can create a cloud account with up to 10 users for free. This will make it easy for people to follow along, as there’s no cost at all.

Third - and perhaps most importantly - Jira has complex logic that sits behind the APIs that we don’t have to re-write in PL/SQL. If this were a real project, this would be absolutely critical, as all transactions from either APEX or Jira is fed to the same API layer, ensuring that we only have a single code base to maintain. And in the case of a SaaS application like Jira Cloud, we don’t even need to maintain it.

For example - if I want to assign a different project code to a Jira Project, all I need to do is submit the update. Jira’s APIs take care of updating the title of all of the issues. Same thing if I delete an Epic - all related issues are updated automatically. Even cooler - any automation that is configured will still fire if it’s triggered by the APIs.

Most SaaS platforms fall into this category - API-first platforms that work regardless where the transaction is originated. APEX can easily adapt and also fit into this architecture as you’ll see as soon as you have your application up & running.

The APEX application that I built only does a fraction of what Jira is capable of. It can create bugs, tasks and epics, assign them to different workflows, add labels to any of them and capture notes. It also has a project-level summary dashboard with charts & reports. Keep in mind here my goal was to create a real-world, practical example to highlight what’s possible with REST Data Services, not build a feature-for-feature implementation of Jira.

Getting Started

Rather than create a series of blogs that have all of the steps outlined to build the solution (there’s just too many), I’m going to take a different approach: I’ll provide the application for download and then blog about what I did, referencing what’s in the application. I believe that this approach will make it easier to follow along, as I probably spent too much time making incremental changes to the UI to get it just the way I wanted it.

You will, however, need your own Atlassian account if you want to test the integration. Fortunately, they are free and take just seconds to set up.

Create a Free Atlassian Account

Like Oracle, Atlassian has a free tier which limits what you can & can’t do, how many users you can have, etc. Basically, you can have up to 10 users and 2GB of data across their platform. For testing and learning, that’s more than enough.

You can read about the specifics here: https://www.atlassian.com/software/free

Let’s walk through the process of crating a new account:

1. Navigate to the following URL: https://www.atlassian.com/try/cloud/signup?bundle=jira-software&edition=free

2. Enter your email address and click Sign Up.

3. A six-character code will be sent to the email that you used to sign up. Open that email and enter the code on the next screen and click Verify.

4. Next, add your Full Name and a Password for your account; click Continue when done.

5. Enter the name of the site. You’ll want to remember this full URL for later when we add the Jira endpoint to APEX. In my case, it’s spendoliniblog.atlassian.net.

6. Next, answer a couple of questions about how you’ll use Jira.

7. When you’re done, you’ll see the Welcome Page, where you can name your first project. Name it anything you like; I took the default “My Scrum Project”.

Take a few minutes to explore Jira Cloud. There’s plenty of documentation and a helpful community on Atlassian’s site that you can refer to, should you get stuck.

Downgrade to Free Edition

You’ll need to make an adjustment to the account that you just created. Technically, you’re in a 30 day free trial for the Premium Tier, which at the time I’m writing this post, is $17/user/month. From what I remember, once the 30 days are up, you can also make this adjustment, but here’s the steps if you don’t want to wait.

To downgrade:

1. Click the gear icon in the upper-right and select Atlassian account settings.

2. Click on the Billing tab.

3. Click Change Plan. On the next screen, click Select Free.

5. Click Downgrade to free.

6. You’ll see the following confirmation message:

Of course, if you want to pay for Jira to unlock more features, have at it. This blog series makes use of only those available with the Free Edition.

Create an Atlassian API Key

In order to call the Jira APIs, you’ll need to create an API key. API keys in Jira are mapped to the user. Thus, if you have 5 users calling the APIs, they will each need their own API key. Makes sense when you have a platform that charges by the user and don’t want the API layer to become a back door that skirts around licensing!

To create an API Key:

1. Navigate back to the home page of your Atlassian account with the following link: https://home.atlassian.com/

2. Click the gear icon in the upper-right and select User management.

3. Click on Account Settings.

4. Click on the Security tab.

5. Click Create and manage API tokens.

6. Click Create API Token.

7. Enter APEX Integration for the Name and set the Expires on to a far-out date. Atlassian lets you set the date up to a year out, so that’s what I chose.

8. Click Create. When the token is displayed, be sure to copy it and store it somewhere safe. We will need this later when installing the APEX application, and this is the only time it will be displayed.

You should now have three pieces of information that we will need later:

1. Your Atlassian hostname

2. Your email address that you used to sign up for Atlassian with

3. Your API Key

Choose Where to Host the APEX Application

You can host the APEX application anywhere that has access to Jira Cloud. I’ve tested it on both apex.oracle.com and APEX on ADB in OCI. It will even work on a local container instance of APEX! If you’re running your own instance behind a VPN, you may run into issues where calls from the database to Atlassian are blocked.

If you’re going to install this application on either ADB-S or apex.oracle.com, you can skip the following step. If you’re installing this on a local container or DBaaS or on-prem APEX, you’ll likely need to execute the following as ADMIN (ADB-D) or SYSTEM (non-ADB) and run the following:

begin
  dbms_network_acl_admin.append_host_ace (
    host       => '[server name].atlassian.net',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_240100',
                              principal_type => xs_acl.ptype_db));
end;
/

Make sure to adjust the value for principal_name if you’re not running APEX 24.1 and change [server name] to the name that you used.

Download and Install the APEX Application

Once you have a place to put the application and have opened up the respective URL for your Atlassian site, all that’s left is to download and install the APEX application.

You will need at least APEX 24.1 for this to work.

1. Download the APEX application by clicking here.

• 💡

  Warning: This APEX application calls the Jira Cloud REST APIs and has not been thoroughly tested. There is a risk that it will make updates that cause issues with your Jira data. Use it at your own risk.

2. Login to your APEX workspace as at least a developer.

3. Click on the App Builder icon.

4. Click on Import.

5. Drag the application export file that you downloaded (apex_and_jira.sql) to the Drag and Drop region and click Next.

6. Click Install Application. No need to select any specific Application ID.

On the next screen, we’ll provide the Remote Server information. Credentials also need to be provided, but we’ll do that after, since not all of the values we need to provide are available here.

7. In the Remote Servers section, change the hostname in the Base URL to what you specified when you created your Atlassian account. The full URL should look like this: https://[servername].atlassian.net/rest/api/

7. Click Next.

8. Click Edit Application.

9. Click Shared Components.

10. Click Credentials.

11. Click Credentials for Jira Projects.

12. Set the values to the following and click Apply Changes.

13. Navigate back to the application builder and run the application. If all was configured correctly, you should see an APEX application with a link to the starter Jira project that you created earlier.

14. Click on the tile for My Scrum Project to see the details. It should be blank at this point unless you added an issue earlier.

At this point, the integration is up and running. You should be able to create issues in either APEX or Jira, reload, and see them on either side.

Caveats

I tried to keep things as generic as possible so that it’s easy to get to a working application. In doing that, there’s a few items in the APEX application that are disabled by associating them with a Build Option and setting that to exclude.

Backlog

While default Jira projects have a Backlog, the default workflow does not. Thus, the “swim lane” on page 10 for Backlog is disabled. If you add another step to your workflow and name it Backlog, you can re-enable this region.

Sprints

Additionally, you won’t likely see your issue on the Kanban Board until you add it to a sprint and start it. Make sure to perform those actions in Jira so that both Jira and APEX display the same content.

Additional rules could be built into APEX to manage sprints as well, but that was out of scope for this blog series.

Priorities

Default Jira projects do not enable the ability to manage the Priority of issues. Thus, the page item for Priority on page 10 has also been disabled. If you add Priority to your issues screen, you can enable it in APEX and it should work, as it is mapped in the REST Data Source.

Next Steps

This post is just the beginning. I’m going to create several more posts over the next few days that walk through how this integration works, tools that you’ll want to have to build similar things, and tips on how to debug issues when they occur. Stay tuned!

Title Photo by Eden Constantino on Unsplash