I have worked with several customers who are struggling to understand how they can extend the UI in Oracle Fusion Apps or Cloud ERP. Tools like OTBI offer options for representing data that is already in the Cloud ERP environment (namely dashboards, infolets and charts). It does not, however, allow you to incorporate mashups of data with other systems.

The solution to this is PaaS. Using Oracle's PaaS offerings, you can embed a custom UI within your Cloud ERP application without your user having to navigate out of Cloud ERP or re-authenticate. Oracle's PaaS solutions, however, are expensive and harder for the traditional Oracle Applications community to understand. We require, therefore, a gateway drug that we can buy cheaply, that we already use and will get us hooked on other PaaS solutions. Enter APEX on the Oracle Exadata Express Service, the Oracle Database Schema Cloud Service or even your own APEX environment (if it is externalized to the internet and running over https).

In this post, I will take you through the basic steps to integrate a simple APEX Report in a new page in Cloud ERP. Do not be fooled by the simplicity of this example, the potential is much farther reaching. This approach opens the door to many other possibilities, including but certainly not limited to:

• An APEX report in an Cloud ERP tab, showing Salesforce data in real time (Via a Web Service call from APEX to Salesforce)
• An APEX data entry form in an Cloud ERP tab that allows users to enter data and post it to other systems via web services in APEX (e.g. Salesforce, Concur, on-premise legacy systems etc.)
• An APEX data entry form in an Cloud ERP tab that displays departmental data from the APEX database mashed up with Cloud ERP data. In this scenario APEX pulls data from Cloud ERP and its local database to present a mashup to the user.

At this stage, I should say the example I am going to outline below is not fully comprehensive. There are a few areas where I would tighten up security (e.g. creating an authentication scheme in APEX) and some steps that were taken for expediency that I might do differently if it were a production application.​

 There are a few things you need to take care of on the APEX side to make this work seamlessly.

• Use the Vista theme, this is the Alta UI used in today's Oracle Cloud Products and fits very well with the Cloud ERP look and feel.
• Create a Custom Page Template. I took a copy of the standard template 'Minimal - No Navigation' and removed the following items from the new template:
  • Remove <div class="t-Header-branding"> and all the div's between it from the Header.
  • ​Remove: ​#APP_VERSION#,  #CUSTOMIZE#,  #SCREEN_READER_TOGGLE# from the Body.
  • This removes the APEX header, Nav Bar etc. from all our pages. Remember, our pages will be running in a tab within Cloud ERP so we don't need the extra stuff.
• The mashup will be running in an iframe. To make this work, we need to change an APEX security setting > Edit Application Properties > Security > Browser Security > Embed in Frames = Allow.
• If you are running on your own server, you will need to import the Oracle Cloud ERP ssl certificate into your database wallet so you can do a call back to Cloud ERP REST and SOAP web services from APEX.

Now that we have the basics in place, we can create our demo APEX page. Create a new page (page 10) with the following attributes:

• Page Authentication: Page is Public
  • Note: This is an example of something we do in a demo only. In reality, we would create an authentication scheme.
• Create a Before Header page processes (more on this below):
  • Name: Parse JWT and Get Username
• Create a region of type 'Classic Report', enter a SQL query just to get some data returned (e.g. SELECT table_name, status, pct_free, num_rows, avg_row_len FROM all_tables WHERE TABLE_NAME LIKE 'A%').
• Create a chart region based on the same query.
• Create the following Page Items:
  • P10_JWT (hidden, value protected = No). This page item will accept the Java Web Token (JWT) from Cloud ERP.
  • P10_JWT_BASE (hidden, value protected = Yes, Session State 'Restricted - may not be set from the browser').
  • ​P10_FUSION_USER (Display Only, Session State 'Restricted - may not be set from the browser')

Before I go too much further I should explain where the Java Web Token (JWT) fits in. I will explain how to generate one from Cloud ERP later in the post. The JWT is essentially a token that is generated by Cloud ERP uniquely for your session. The JWT will be passed to APEX in the Query String. The JWT is composed of 3 '.' delimited segments. The first segment is the JWT header, the middle is the actual payload, and the last section is the HMAC256 encoded signature. Each segment is Base 64 encoded.

Here is an example of the middle segment (base 64 encoded):
eyJleHAiOjE0Nzg4MzE1MDAsImlzcyI6Ind3dy5vcmFjbGUuY29tIiwicHJuIjoiRklOX0lNUEwiLCJpYXQiOjE0Nzg4MTcxMDB9
Which un-encoded, looks like this:
{"exp":1478831500,"iss":"www.oracle.com","prn":"FIN_IMPL","iat":1478817100}

It contains the expiration, in Epoch/Unix time format (exp) and the user name (prn). We can use this user name to set context in APEX. But that's not all, the entire JWT can also be used ​to authenticate web service calls back to Cloud ERP (more on that later).

The page process is going to take care of accepting and decoding the JWT. I have include some code snippets which will be useful when building your own.

Get the JWT from the URL Query String:
  l_query_string  := owa_util.get_cgi_env('QUERY_STRING');

When Cloud ERP calls APEX, it will pass the token as ?jwt=<token> so we have to remove the 'jwt=':
  l_jwt_start_pos := INSTR(l_query_string,'jwt=')+4;
  :P10_JWT_BASE := SUBSTR(l_query_string, l_jwt_start_pos, 10000);

To get the payload from the middle segment of the JWT, we can convert the JWT to a PL/SQL Array:
  l_jwt_arr       := APEX_UTIL.STRING_TO_TABLE(:P10_JWT_BASE, '.');

The payload is in the 2nd row of the array. Finally, we need to un-encode and then the parse the JSON to get the (prn) or username:
  l_prn_json := UTL_ENCODE.TEXT_DECODE(l_jwt_arr(2), 'UTF8', UTL_ENCODE.BASE64);
  APEX_JSON.PARSE (l_prn_json);
  :P10_FUSION_USER := APEX_JSON.get_varchar2 (p_path => 'prn');

Making Sure the JWT is Authentic
At this stage we know the Fusion Username but we could have fabricated a JWT to get us this far. We should make a call back to Cloud ERP, using the JWT as a Bearer token, to make sure the JWT is recognized by Cloud ERP. At time of writing, there is not a REST based 'who am I' type service in Cloud ERP but we can make use of either a HR or Projects REST service which are available in R11 of Cloud ERP.

e.g. End Point for HR employee REST Service: https://<<host>>/hcmCoreApi/resources/latest/emps/1

We know employee id 1 does not exist, but that is not the point. We just need to make sure we don't get a http response of 401 (un-authorized). It is works, we should expect a 404 not found response.

Calling the Cloud ERP REST Service
  l_url := 'https://<<host>>/hcmCoreApi/resources/latest/emps/1';

  -- Set the HTTP Headers and the Token P10_JWT_BASE is derived from our page process above
  apex_web_service.g_request_headers(1).name  := 'Authorization';
  apex_web_service.g_request_headers(1).value := 'Bearer '||:P10_JWT_BASE;
  apex_web_service.g_request_headers(2).name  := 'Content-Type';
  apex_web_service.g_request_headers(2).value := 'application/vnd.oracle.adf.resourceitem+json';

  -- Call the REST Service
  l_clob := apex_web_service.make_rest_request(
        p_url         => l_url,
        p_http_method => 'GET');

  -- Get the HTTP Response Code. 404 means the JWT was valid but the service could not find the employee record (which is fine for our purposes).
  l_http_status_code := apex_web_service.g_status_code;

Now that we have an APEX application ready to be called we need to login to Cloud ERP to complete the mashup. There are several steps to this:

1. Create a new Third Party Application
2. Create a new Page Integration
3. Locate the new Page in the Application Structure
4. Adjust the size of the new Custom Page

Navigation
Setup & Maintenance > Pull out the Tray on the Right Side > ​Manage Custom Setup Content > Manage Third Party Applications > Click the Plus Icon to Create a new one

The URL is the base URL to your APEX environment without the trailing '/'
e.g. https://www.example.com/ords

First, create a new Sandbox, then create the Page Navigation as follows.

Navigation
> Navigator > Tools > Page Integration > Click the 'New Page' button

Enter a Name, select an Icon then select an Application Role for which the new page should be available. Next, click the golden key icon and another popup will appear, at which point, enter the following:

• For Web Application, select the third-party application created above.
• For Destination for Web Application, enter the end of the APEX URL as follows:
  • f?p=101:10:::::P10_JWT:
  • Where 101 is the application number and 10 is the page number.
  • The colons are meaningful in APEX URLs so don't miss any out.
• Finally, enter jwt as the Secure Token Name and then click Save and Close

To place the icon for our new page on the home page of Cloud ERP, follow these steps:
Navigation
> Navigator > Tools > Structure
Find and Expand 'Sales Performance Aux' and you should see your new page. Click the right Chevron and Select (Top Level). Now click on the Home Page and you should see your new icon.

You may want to adjust the height and width of the iframe. To do this, click the icon for your new page which is located on the Home Page. When the page opens, navigate to > Your User Name > Customize Pages. Size the window the way you want then click Close.

The above steps may seem complex at first glance. But when you consider we just integrated a highly sophisticated web based PaaS service (APEX) with Oracle Fusion Apps / Cloud ERP, it's pretty amazing. Also, don't forget that APEX itself has the ability to consume SOAP and REST services as well as host REST web services. That added to the native database scheduling features may be all you need for integrations. But thats not all folks, with the Exadata Express service, prices start at just $175/month for 20GB storage, 3GB PGA and 3GB SGA for a service that requires no configuration.

This all goes to show how versatile, powerful (and sometimes underrated) APEX in the Cloud is. In my opinion the Cloud will be APEX's coming of age and I expect many more great things to come.

Running ORDS in standalone mode is not supported for production environments. This means you need to run ORDS in a J2EE Web Server. At time of writing, the three supported J2EE web servers are Weblogic, Tomcat and GlassFish. Of these, only Tomcat and GlassFish are Open Source (read free). Oracle's view on support is that it supports running ORDS on these web servers but does not support the web servers themselves. This means that if you have issues with ORDS on these servers you will have to prove ORDS is causing the issue and not the web server.

The goal of the speed comparison is to compare the two web servers under moderate loads. In view of this the tests are not overly complex or load intensive. The load will be generated from number of simultaneous 'users' and repetitions. The idea being to compare them not break them.

​APEX Page Render
A basic APEX public page with three regions. A region with a classic report, a region with 5 form fields and a static text region. This test is aimed squarely at how the web server handles APEX page requests. The test is run 25 concurrent threads and repeated 10 times for a total of 250 transactions. The siege (see below for details on siege)  command run was:
siege -c 25 -r 10 -d 0.1 -m "ApexDynamicPage" -f /home/ec2-user/siege/ApexDynamicPageURL.txt

ORDS REST Get Service
A basic GET service with the following SELECT (no OAUTH security enabled):

select owner,object_name
from   all_objects
where  object_type = 'SEQUENCE'
and    owner  = 'MDSYS'

This is the same SQL used in the APEX page. Under ORDS 3.X calling a REST service does not rely on APEX at all. This test is focused purely on throughput from a REST perspective. The test is run using 25 concurrent threads and repeated 10 times for a total of 250 transactions. The siege command run was:
siege -c 25 -r 10 -d 0.1 -m "ORDSRESTGet" -f /home/ec2-user/siege/ORDSRESTGet.txt

Minified CSS File
I used the minified version of the apex ui css 'apex_ui.min.css'. The file is located in the Apex images folder and referenced in the ORDS i.war file as: i/css/apex_ui.min.css. The file is 108,694 bytes. The test runs in 100 concurrent threads and is repeated 5 times for a total of 500 transactions. The siege command run was:
siege -c 100 -r 5 -d 0 -m "ApexStaticCSS" -f /home/ec2-user/siege/ApexStaticCSSURL.txt

​Static HTML File
I created a small static HTML file and placed it in the root of the respective web server. The file is 3,711 bytes. The goal of this file is raw throughput of the web server as APEX and ORDS are not involved in serving this page at all. The test is run 100 concurrent threads and repeated 5 times for a total of 500 transactions. The siege command run was:
siege -c 100 -r 5 -d 0 -m "StaticHTML" -f /home/ec2-user/siege/StaticHTMLURL.txt

Servers
All of the servers used were running on the us-west availability zone on AWS. They were all running the AWS flavor of Linux except the DB Server which was running OEL.

• DB Server - t2.large (2 cpu, 8gb Ram)
• App Server - t2.small (1 cpu, 2gb Ram)
• Test Server - t2.small (1 cpu, 2gb Ram)

​Software
I used the latest stable versions (at time of writing) of all the software involved (except the DB):

• Database 12c (12.1.0.2.0)
• ORDS 3.0.8
• APEX 5.0.4
• JDK8 (1.8.0_111)
• GlassFish Server (Web Profile) V 4.1.1
• Tomcat 8.56

In order to eliminate network as much as possible, I did not want to install the load test software on my laptop. This ruled out desktop tools such as JMeter. In view of this I installed Siege on an AWS EC2 server in the same region as my App Server and DB Server. For a command line tool, Siege comes with a number of features included with a number of the more sophisticated tools. I used version 3.1.4 of Siege in my tests.

Thanks for sticking with me this far! I do believe, however, it is important to lay out the method I used so you can understand what was and what was not tested. This allows you to more easily apply the results to your situation. First of all, let's look at the raw results (winning metrics are highlighted in green):

As you can see, generally it was a pretty close race. This is not totally unexpected. For ORDS, these web servers are acting as a J2EE container and they are both using the exact same version of the Java JDK.

Apex Page
Generally Tomcat seems to be about 3% faster than GlassFish except for in the area of concurrency where the difference was only 1.45%.

REST Service
Again Tomcat is the victor, this time by a larger margin of around 7% for response time, elapsed time and transaction rate. The two are almost exactly matched for throughput (mb) and concurrency.

Compressed CSS
GlassFish fights back here with a significant win over Tomcat of between 12% and 15%. I believe this shows that GlassFish is much better at serving larger files (the css file is about 100k).

HTML File
Once again Tomcat wins by around 3% except for the area of concurrency where they are again almost exactly matched.

Overall
One deduction from the above (and I am sure you have your own) is that the two are very well matched except that Tomcat seems to be better at thread handling. What I mean by this is that it is getting through the volume of transactions and switching between them faster. But, when there is more work to do (in the case of the larger css file) GlassFish is performing better.

So which web server should you choose? Based on the results of my testing, while Tomcat seems to be generally slightly faster, there is really not much in it.

While not having a clear winner seems like a bit of a let down (it was for me), all is not lost. It does mean that I can now provide my 'client preference' advice with some empirical evidence to back it up.