​REST enabled SQL was first introduced in ORDS 17.4. It is an important feature in the continued evolution of ORDS as a product. REST enabled SQL fits very well into Oracle’s Cloud mantra, in that it enables functionality much akin to a database link but without incurring the resource overhead and the security shortfalls of database links. It essentially provides developers to run Queries, DML, DDL and PL/SQL  statements against an Oracle database via a REST interface.

GraphQL is a relatively new open-source technology, introduced by Facebook. GraphQL is a data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data.

​While the relationship between GraphQL and REST enabled SQL may not be immediately obvious, I believe there are many similarities and plan to describe them in this post.

From Wikipedia: “GraphQL is an open-source data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data. GraphQL was developed internally by Facebook in 2012 before being publicly released in 2015. On 7 November 2018, the GraphQL project was moved from Facebook to the newly-established GraphQL foundation, hosted by the non-profit Linux Foundation”.
 
Essentially, GraphQL allows a front-end developer to generate a dynamic data query without needing to know the underlying database technology. Much like REST services therefore, GraphQL abstracts the developer from the underlying database. Because of its dynamic nature, however, GraphQL removes the need to build a REST end-point to cover every resource and data element. Instead, you build one back end end-point to handle all requests. You then dynamically build your query on the front end (using a special JSON format) and submit your query to the server for execution. This avoids the overhead of calling multiple REST resources and then consolidating the responses.
 
GraphQL is not a database, it is a query language that allows you to inquire on data. It requires a backend (think middleware server) that handles the query and dispatches the query to the appropriate database(s) to get the actual data. These databases could be MySQL, Oracle, Postgress etc. An example of a GraphQL API server is an open source product called Prisma.

​GraphQL allows the client to decide exactly what data it wants. If you are just looking for the customer name and number, you can limit your GraphQL query to just those fields. Most REST services would force you to retrieve all attributes of a customer this bloating the payload.
 
GraphQL is strongly typed and each type describes a set of available fields. This not only facilitates descriptive error messages but also allows you to inquire on the types that are supported by the database.
 
So, what does a GraphQL query look like?

​You can see that the query itself defines the shape of the data that should be returned.
 
This all sounds great but the GraphQL server does not come for free. It obviously needs to understand everything about the underlying databases. Forgetting the infrastructure requirements, the work of mapping all of that data can be a big hurdle.
 
So how can Oracle RAD stack developers achieve some of the benefits of GraphQL using the software they already own? ORDS and REST enabled SQL to the rescue.

REST enabled SQL allows a client to send any SQL statement to an Oracle database and have the database execute that statement.
These statements can include:

• SELECT, UPDATE, DELETE
• PL/SQL Blocks
• SQL*Plus and SQLCL Commands

​Attributes of REST Enabled SQL that are similar to Graph include:

• The client can inquire on what tables and views are available in the database by inquiring on the data dictionary.
• The client can inquire on the structure of specific tables and views and receive detailed information on each column (i.e. columnName, jsonColumnName, type, precision, scale and isNullable)
• The client can request exactly what data it wants by defining it in the SQL statement.
• REST enabled SQL uses a single end-point “https://<HOST>/ords/<SchemaAlias>/_/sql“ for all inquiries (not multiple separate REST services).
• Because it is using the Oracle database, the data is strongly typed.
• You can join multiple tables (an or use views) to consolidate data into one request that would otherwise have required you to call multiple REST resources and then merge the data together.

 
​An interesting point to note here is that much like with GraphQL, a ‘smart’ client can navigate an inquire on a database with no pre-programmed knowledge of the structure of the database.
 
Other attributes of REST Enabled SQL:

• Queries can be expressed as a simple select statement ‘SELECT SYSDATE FROM DUAL’ or by passing a structured JSON document.
• REST enabled SQL can be secured by:
  • Schema Authentication (the database schema user name and password).
  • OAuth 2 Client Credentials (client gets a token and then uses that token for subsequent calls)
  • First Party Authentication (Basic Authentication). Utilize a user and password created in Oracle REST Data Services with the SQL Developer role.

 
Probably the biggest mismatch between GraphQL and REST Enabled SQL is that REST enabled SQL assumes the client/developer knows the Oracle database. While this may sound like heresy to the open source world, in the RAD stack world this is not a bad thing. In fact, embracing the power of the Oracle database is something every RAD stack developer should be looking to do.

​To enable REST enabled SQL for your instance, you need to add the following entry to the ORDS parameter file on your server (typically defaults.xml):
<entry key="restEnabledSql.active">true</entry>
 
In order to use REST enabled SQL for a specific schema, you also need to REST enable the schema, just like you would to allow creation of any ORDS services in a schema.

• Connect to the Schema
• Run the command: exec ords.enable_schema;

 
Important Note: As mentioned in the documentation “Enabling the REST- Enabled SQL service enables authentication against the Oracle REST Data Service enabled database schemas. This makes the database schemas accessible over HTTPS, using the database password. Oracle highly recommends that you provide strong secure database passwords”.

In view of this warning described above, I advise that you create an entirely separate schema for use with REST enabled SQL (and make the schema password very complex). This at least limits your exposure to that one schema. Added to that, you should also avoid using Schema Authentication (i.e. use schema name and password) whenever possible and instead use OAUTH2 Client Credentials Authentication.
 
If a third party needs to access your database using REST enabled SQL, setup OAUTH2 Client Credentials Authentication by performing the following steps:

• Create a new schema e.g. SYSTEMX
• Login as SYSTEMX and run ords.enable_schema to enable ORDS in the schema
• Create the appropriate grants to the schema SYSTEMX for the tables and views you want to provide access to.
• Create an OAUTH client and assign the role ‘SQL Developer’
• Provide the client with the client_id and client_secret values from the table ORDS_METADATA.OAUTH_CLIENTS.
• The client would then use the client id and secret to get a token and use that and would never know the schema password.

​Unfortunately, even when you configure OAUTH2 Client Credentials Authentication you can still use Schema Authentication. I would like to see a way of turning Schema Authentication off for a schema that has OAUTH2 Client Credentials Authentication.

In this example, I am going to be using the ‘application/sql’ Content-Type. This indicates to ORDS that it should expect a SQL statement in the payload. You can also use ‘application/json’ and pass a structured JSON document. You may notice that I am using OAUTH2 Client Credentials security and passing a token with each call.
 
I have created a table called atx_ev_charging_stations which contains a list of the electric vehicle charging stations in Austin. The first think I need to do is get a definition of the table. REST enabled SQL will always return a metadata array in the response, this describes the columns in the SELECT statement. All I need to do is pass a SQL statement that I know will return no rows and I can get the metadata e.g. 'select * from  atx_ev_charging_stations where 1=2'

​I have just shown the first column in the metadata array, but you can see from this the metadata information that is returned about each column.
 
Now I understand the table structure I can form a query to get the charging station name and first line of the address for charging stations at apartments in the zip code 78704.

​Notice I am only asking for two columns from the table. An equivalent REST resource would need to contain all of the columns in order to fulfill the requirements of all inquiries on this resource:

​In this post, I provided some insight into what GraphQL is and how it can be compared to ORDS and REST enabled SQL. I also provided a high-level overview of REST enabled SQL along with a basic example. From this, I hope to convey what a powerful feature REST enabled SQL is and how you may be able to use it in your applications.

​I have been working with Oracle e-Business Suite (EBS) since version 9.4 (that’s character mode!). Although EBS has come a long way since then, even the newer releases are starting to look long in the tooth. In this post I will explore a number of use cases where Oracle APEX was used to bring the joy back to ERP without spending a whole lot of money.

​Although many companies are moving their ERP to the cloud, many cannot because cloud ERPs offerings do not yet provide all of the features, they need to run their business. This leaves companies running on-premise ERP systems like EBS version 12.1.3 in limbo. Do they try and upgrade to version 12.2 for an incremental improvement in UX or do they wait until key cloud features are available and then move to the cloud? If they wait, how do they explain to their users that the technical acrobatics required to run Java from a browser these days is worth the effort? The user base for such companies are in full revolt and they won’t stand for it any more. This is especially challenging for companies that have introduced edge application such as HR in Workday. Users now know what is possible and want more of it.

So, how can an IT department provide a significant improvement in UX to EBS users for zero additional software cost? Oracle APEX is a no cost option of the Oracle database. APEX provides you with all the tools you need to build beautiful, scalable apps in a single, extensible platform, which runs as a part of Oracle Database. In this case it will run in the same database as EBS, providing zero latency data access.
 
What you will need

• An application server (or VM) to run Tomcat and ORDS (e.g. 25GB Disk 4GB Memory, 1 CPU)
• Install the latest version of APEX on the EBS Oracle Database
• Install Tomcat and ORDS on the Tomcat/ORDS server

Note: There is no additional cost for any of the software mentioned above.
 
I am not advocating re-writing EBS in APEX, far from it. What I am advocating is taking a few key processes that have high visibility and high impact to your organization and using APEX to provide a modern easy to use interface.

I have found that using APEX to extend EBS makes it easier to introduce other technologies that can also transform the way users interact with EBS. For example:

• Voice interfaces to EBS with Alexa.
• Utilizing SMS to communicate with your employees (and process responses).
• Introducing PaaS products like Exadata Express to extend EBS outside of your firewall.
• Leveraging AWS S3 for document storage.
  
• Provide custom mobile interfaces for users to interact with EBS.

​In all likelihood, your organization has an annual review and performance management process. If you do then it is also likely that most people in your organization participate, making it a highly visible process. Oracle EBS has a performance management module that is fully functional and integrates well with Oracle Human Resources. The User Experience, however, is not something you can be proud of. In addition, you are probably spending a significant amount of time managing issues related to this poor UX.
 
Last year JMJ developed an APEX application to replace the employee facing side of EBS performance management. This application acted as a skin on the underlying Oracle performance management module, utilizing standard Oracle APIs to create and update records in the EBS data model.

1. Employees logged in directly to the APEX App, using their EBS credentials. The App had a URL that was easy to remember and was not associated with EBS. The UX for the app was completely unlike EBS, in fact it more closely resembled a consumer application.
2. On the back end, the HR department used the standard EBS functionality for tasks such as moving appraisals to compensation workbench.

 
The overall impact was dramatic. People actually liked using the application, not only did this make employees feel good but it increased participation in the process.

My second example involves taking a critical business process and building a custom application to manage that process in a way that would not be possible with standard functionality. To some, this may sound like heresy. In fact, for the 18 years I worked for Oracle Consulting extending EBS and ERP Cloud, my mantra was always don’t customize unless you have to. The key here is the word ‘have to’. If you can save a million dollars a year (or make an additional million dollars a year) by building a product that costs 250 thousand dollars a year to maintain and upgrade, then you have to do it.
 
JMJ Cloud recently built an APEX application that managed the receipt and processing of material at over 40 manufacturing plants. In doing this, we built a whole new UI for plant staff to greatly simplify their interactions with EBS. Behind the scenes, however, were able to use significant pieces of EBS functionality to avoid having to re-invent the wheel.

1. Plant users’ gain access via either a native iOS mobile application or an APEX App. These users perform plant operations such as receiving material, creating sales orders, logging production etc.
2. Back office and corporate staff log directly into EBS to perform back office processing. e.g. pricing and booking orders, creating invoices etc.

 
Simplified overview of the business process:

• Material delivered by customer for processing:
  • User logs into APEX and created a new job.
  • The APEX UI for creating the job pulls customer information directly from EBS. When a job is created, an EBS Sales Order header is also created.
  • During the receiving process order lines are added to the EBS Sales Order via an APEX page. The receiver enters just 3 fields per order line in the APEX application making data entry simpler and faster.
• As material is processed production data is posted via the APEX App, to the associated sales order lines in EBS.
• Once the sales order is priced and booked, invoicing and collections is handled from standard EBS.

 
The APEX application in conjunction with a native iOS app that speaks to EBS using Oracle REST Data Services (ORDS), means the majority of users at the plant do not have to be concerned with EBS at all. In an environment with a high turnover of staff who have limited technical skills, having an easy to use and simplified UI is critical.

As I mentioned earlier, Oracle EBS is one of the most feature rich ERP platforms out there. This complexity can be both a blessing and a curse. A single Oracle module can take months of effort to implement and you may end up using less than 25% of the full functionality of that module. The problem is you are paying for 100% of the functionality. If you could build the pieces of the module that you really need and then integrate that with other aspects of EBS you could end up saving money and providing a more modern user experience at the same time.
 
One example of this is a Capital Expenses Tracking system which JMJ Cloud recently built for an EBS customer. This was a relatively simple 8-page (4 table) application that allows users to enter details of a Capital Expenditure request and then submit the request for approval. Having the new App build on the EBS database, allows us to leverage EBS for much of the heavy lifting.
For example:

• Authenticate users using Oracle EBS credentials
• Use EBS view, value sets and lookups as the source for List of Values to reduce dual maintenance and increase consistency. (e.g. List of GL account segments, List of GL periods)
• Utilize Oracle Workflow to handle notifications and approvals of submissions
• Provide list of Capital Project budgets (from Oracle Projects in EBS) to associate a CAPEX request with

​Oracle EBS is one of the most (if not the most) feature rich ERP application on the market. Often times, these features have come at the expense of UX and in 2018, the UI is showing its age more than ever. By using APEX to replace the UX of key processes in EBS, you can bring the joy back to ERP for your users. Applications that bring users joy are known to have higher adoption rates and bring more smiles. Using APEX to modernize your existing EBS implementation can buy the time you need for Cloud offerings to match the functionality that you need to run your business. 

​I have been using APEX since version 4. I have used it to develop extensions to E-Business Suite, Oracle ERP Cloud and also for Custom Applications. During this whole time, I have come to dread the question that clients almost always ask, “How do I print from APEX”? In this post I will tell you why I think APEX Office Print (AOP) gives APEX developers a chance to respond to that question with their heads held high.

APEX Office Print offers a solution for generating highly formatted documents and reports in Word, Excel, PowerPoint, HTML and Plain Text formats. It comes in an on-premise and cloud version. You send AOP a template and a JSON payload and it returns a formatted document. On the backend, AOP is architected as a web service which means it is completely technology agnostic (one of its key differentiators). It means you can use AOP from any technology that can call a web service (not just APEX). It is called APEX Office Print for a reason though so let’s talk about that. AOP comes with a Plugin for APEX which provides tight integration with a number of APEX components (e.g. Interactive Report, Interactive Grid). Using this integration, downloading an Interactive Report to Excel can be as simple as adding a button to an APEX page and creating a template with a single tag. Read on to see some more in-depth examples that explore many other capabilities of this interesting product.

The obvious product to compare AOP with is Oracle’s BI Publisher. For me, the primary differentiator for AOP is its availability in the Cloud. This makes it accessible from just about anywhere. In fact, I am working at a client now where we are using AOP in the Cloud from APEX on-premise and from APEX running on Exadata Express. There is even talk at the same client of calling AOP from a native iOS App.
 
Other AOP differentiators include:

• Tight integration with APEX.
• JSON data payloads (as opposed to the bulkier XML that BI Publisher uses).
• Powerful and easy to follow templating syntax.
• Comprehensive demo APEX application. This was a great help when I learned AOP.
  
• Reasonably priced (especially when compared to BI Publisher).

I won’t be diving too deep, but I do want to demonstrate some ways we are using AOP for our clients. Hopefully, these examples will illustrate why you should consider AOP as part of your APEX printing strategy.

Inspection Report
One of our clients’ needs to produce documents (in pdf and Word) detailing the inspection of product before it is shipped to the customer. The document shows order details, material condition information and up to a hundred readings taken from a digital quality gauge. The document is printed at the plant and then emailed to the customer. With so much information coming from many different sources, we decided to use the following approach to generate the document:

1. In APEX, the user selects the job to print, checks the output format (pdf or Word) and clicks a custom ‘Print’ button.
2. Behind the ‘Print’ button, we call a custom PL/SQL procedure does the following:
   • Calls a function to generate the JSON payload for the report. This function calls a number of functions and procedures to gather the data necessary for the report.
   • Fetches the appropriate AOP template from a database table for the user’s logged in language.
   • Calls the AOP procedure ‘aop_plsql_pkg’ with the JSON and the template. The ‘aop_plsql_pkg’ gathers the template and JSON and calls the AOP web service which returns a BLOB with the formatted document.
   • Downloads the output to the users’ computer (optionally emails the document to the user).

 
Note: In the above example we have not used the AOP APEX Plugin at all. This illustrates you can use the ‘aop_plsql_pkg’ package to call AOP directly from PL/SQL.

Generic Interactive Report Excel Downloads
The example illustrates AOPs tight integration with APEX via their Dynamic Action Plugin. One of our clients has an APEX application with over 150 pages. Amongst these pages are about 50 interactive reports and interactive grids. If we wanted to develop an ‘.xlsx’ download for all of these reports using BI Publisher, we would have had to:

1. Develop 50 different BIP templates and upload them to the ‘Report Layouts’ section in ‘Shared Components’ in our APEX App.
2. Create a copy of the 50 underlying SQL statements for each of the reports to the ‘Report Queries’ section in ‘Shared Components’ in our APEX App.
3. Add code to each of the 50 pages to call these BI Publisher reports.

The duplication of the report SQL is the exact opposite of ‘DRY’ and represents as significant increase in maintenance and upgrade costs.
 
AOP to the Rescue
Using AOP, we can create a single Excel template with the company logo, a nicely formatted title block and a single token ‘{&interactive}’. This template can be used for all 50 of our interactive report Excel downloads.

​By adding the code below to our APEX pages, we can use this template on any interactive report in our application, significantly cutting down on the number of templates we need to maintain.

​Code from the Init PL/SQL Block:

The above code does the following:​

1. Sets the download filename using a global variable.
2. Sets a global variable with APEX Items so they can be referenced in the AOP template.
3. Sets the formatting for the table headings in the output and even sets alternating row colors.

Sample Output:

Employee Document Receipt Confirmation
My final example shows how you can use AOP in the Cloud to generate highly formatted documents from on-premise or Cloud based applications. In this instance, the customer requested that their employees be able to confirm receipt of key documents (e.g. NDA, Employee Handbook) in the Cloud and also generate a pdf copy of their acceptance for the employee to download. In addition to this, they also wanted to generate a copy of the pdf to attach to the employees HR record in EBS on-premise.
 
APEX running on Exadata Express was used to show the employee the document and to call AOP to generate a pdf of the document acceptance for the employee to download.
One accepted, the on-premise system pulled in the acceptance to the local database, and then used the same AOP template to generate another pdf copy of the document acceptance and attached it to the employees official HR record in EBS.

Architecture

Here are some other potential use cases for AOP:

• Using AOP from a button in APEX to generate a report and email it instead of downloading it.
• Calling the AOP web service from a native iOS App in order to generate a pdf to display to the user on an iPad.
  
• Calling the AOP web service from a customer portal (built in a non-Oracle technology) to generate a pdf copy of an invoice for a customer.

There are a couple of things I would like to see from AOP in the future:

• Ability to store templates in the Cloud e.g. AWS S3 and have AOP go directly to the Cloud to get the template.
  • You can store templates in S3 today and reference them via a URL. The problem is AOP makes a call from your local database to get the template before the AOP web service is called.
  • Ideally, we would pass a template URL and a JSON document to AOP. AOP would then fetch the template directly from S3, without the extra round trip.
• Free development mode credits.
  • Developing a template-based report is an iterative process. You often go through many iterations of changing the template, running the report, changing the template and running the report again. A complex report could take more than 100 iterations. This can quickly eat into the monthly AOP plan quota.
  • I would like to see a development mode that did not count against your quota. I would even be OK if the result was watermarked in development mode.

We have only been using AOP for three months, but we have already used it to build several mission critical documents and reports. It has been reliable, performant and fills most of the printing gaps left by Oracle in the base APEX product. If it continues to develop at the rate it is today, I believe it will become the go to solution for reporting from APEX. At time of writing a free tier was introduce giving you 100 free credits per month. I encourage you to go try it out!