Oracle APEX – Let’s integrate with Jira! Part 1

6 September 2019 Lech Cieślik

Jira is Atlassian’s software for error tracking and project management.  Users working with any APEX application often need to report an error regarding the application usage or data they work with. Instead of logging in to Jira, looking for a specified project, using a wizard that will allow to create a new request (name, type, description, request assignment, etc.), the user can easily use a solution implemented fully in Oracle Application Express, which we will introduce step by step in a new Oracle APEX article series – Let’s integrate with Jira! 

In the examples below, we will use the Oracle Database 12c Enterprise Edition Release database and the Oracle SQL Developer Version 18.2. tool. 

With this blog entry, we will start a series of three articles describing how to integrate the Oracle APEX application with Jira step by step. The articles will be divided into three parts: 

  1. In the first part, we will present inner workings of OAuth authorization based on the token exchange process commonly known as the “OAuth Dance”. The end result will be generating a token that will allow us to manage Jira’s data using the REST API. 
  2. In the next step, we will focus on using Java in Oracle, which is necessary for the implementation of the encryption used in the “OAuth Dance” process. 
  3. In the last article in the series, we will present an example of using the Jira API to create your own Jira request from the Oracle Apex application.

What is OAuth and “OAuth Dance”?

Authorization with Jira can be implemented in one of two ways, either through the use of Basic Authentication or via OAuth 1.0 standard. Each approach has its pros and cons.  The first method is very simple, but in the request header, we send the user’s encrypted login and password. The second one is more complicated to implement but doesn’t need to send the user’s password or the login information in the request header, protecting the user’s privacy. For this reason, we will focus on the second approach in this article.

OAuth is an authorization standard that allows users to share applications information stored with other service providers without entering a password. The whole process is done by exchanging tokens that are received in response to HTTP requests sent to a specific address (table below). This process is known as “OAuth Dance” and can be described in three steps: 

  1. The user submits a request through the Request Token URL to generate a Request Token. 
  2. The user is redirected to the Authorize Token URL to authorize the previously received Request Token. 
  3. The user submits a request through the Access Token URL to generate an Access token. Using Access Token we can process data provided by Jira (Jira API).
Request Token URL /jira/plugins/servlet/oauth/request-token
Authorize Token URL /jira/plugins/servlet/oauth/authorize
Access Token URL /jira/plugins/servlet/oauth/access-token

To learn more about the “OAuth Dance”, you can read Jira’s documentation:

In the next part of the article, we will go from theory to practice and describe specified steps of “OAuth Dance”.

“OAuth Dance”: some details

Sending the request and receiving the reply is based on using the HTTP protocol. For their implementation we will use the PL/SQL library utl_http. The most important thing will be to correctly define the headers and the body of the request we generate that will allow us to get the appropriate token.

“OAuth Dance” step 1: Request Token & Redirection to Authorization URL

Obtaining a Token Request is the first step of “OAuth Dance”. We need to define basic headers such as user-agent and Content-Type in the HTTP POST request. The Authorization header should include the parameters shown in the example below:

Let’s stop for a moment to think about the signature. All requests must be signed with a private key (located on the side of our application), and then verified with a public key (located on the side of Jira). OAuth defines three signature methods: HMAC-SHA1, RSA-SHA1 and PLAINTEXT, however, Jira only uses RSA-SHA1. So the signature is nothing but a set of connected parameters (so-called base string) signed with a private key. We can create a base string as shown below, but we’ll write more about implementing the signature in the next article.

After sending the request, we will receive a response containing the Request Token:

“OAuth Dance” step 2: Authorization

After generating the Request Token, it should be added to the Authorization URL: /jira/plugins/servlet/oauth/authorize?OAuth_token=DdNCjYhyOfUdHUDXnYqc3RVPN2MqYy3d. The described URL redirects to the authorization screen:

After clicking the Allow button, the user will be redirected to the URL defined by the oauth_callback parameter in step 1. In addition, the oauth_verifier parameter will be added to the URL which will be used to get the Access Token in the next step.

“OAuth Dance” step 3: Access Token

We have already generated a Request Token and we have passed the authorization process, which result is generated oauth_verifier parameter. The only thing left for us is to send a request to generate an Access Token that will allow us to process data in Jira in any way we like. The request should be created according to the example presented in step 1. The only changes that we make are in the URL: /jira/plugins/servlet/oauth/access-token (notice the lack of the “oauth_callback” parameter and two new parameters present in the Authorization request header: 

  • oauth_token – Request Token returned in response to the request in step 1 
  • oauth_verifier – the parameter returned after authorization in step 2 

We must also remember to modify the parameters used to create the Base String which must be signed with the private key.

As a result of sending the request, we will receive a response containing the Access Token:


In the article, we described the OAuth 1.0 authorization standard and presented the process of exchanging tokens via the so_called “OAuth Dance”. We used PL/SQL libraries for the token exchange implementation, which allows us to use the examples in our Oracle Apex processes. In the next article, we will describe in more detail the problem of the required digital signature and the process of signing a request in Oracle.

Tagged with: , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!