​As you may be aware, Exadata Express is coming to the end of its life. If you like us have found this platform both useful and affordable, then you will be wanting to know what’s next? Where in the cloud can I deploy my RAD (REST, APEX, Database) stack now? In this post I will explore an alternative which we have implemented here at JMJ Cloud.

​We have been using Exadata Express and JMJ Cloud for more than 2 years. During that time, we helped five of our clients implement Exadata Express. Use cases ranged from custom cloud point solutions to ERP Cloud PaaS extensions. I have also blogged about this platform on a number of occasions. Exadata Express offered a highly functional PaaS solution for as little as $175 a month. We (and our customers) were very happy with it. In fact, I thought this product was a sign that Oracle had finally turned the corner to becoming a major cloud player. It provided a very low barrier to entry with more feature rich alternatives ready and waiting when your business grew into them. This is essentially how AWS became so successful. One day you start with half a dozen EC2 servers for a couple of hundred dollars a month and a few years later you are spending a million dollars a month (think Netflix). Oracle on the other hand typically starts by charging a lot and then work their way up from there.

A month or so ago, our Salesperson emailed us… Here is where I need to pause for a small rant. Why on earth, if you are selling automated cloud solutions should I ever have to deal with a Salesperson? I want a server; I know what size server I want, and I know how much I want to pay for it. From my perspective, having a Salesperson in the middle of this is expensive from Oracle’s point of view and extremely frustrating from my point of view. OK, back to the story… Our Salesperson reached out and said we could not renew our Exadata Express contract and oh, by the way, you have a month to get off the platform. After a little discussion (i.e. an hour of my time on the phone) we converted that 1 month into 3 months. We bought ourselves some runway, but we still needed another platform. Enter the Salesperson (again). After another one hour of my time he proceeded to explain the wonders of the Autonomous Database Cloud Service. Don’t get me wrong, this service shows a lot of promise and it is going to be great for our larger customers. At more than $1,200 per month, however, it is not going to fly for us or our smaller customers wanting to run the RAD stack in the cloud. It is nearly 7 times more expensive than Exadata Express. If you wanted to use Autonomous Database with APEX for a Cloud ERP extension and you need a DEV, TEST and PROD instance that is $3,600+ per month instead of $600 / month.
 
To add insult to injury, there is not even a button push migration path from Exadata Express to Autonomous Cloud. This means that even if you have the money to pay for Autonomous Cloud, you are going to have to fork more money to migrate.

I would love nothing more than to say I whole heartedly recommend folks move to the Autonomous Database Cloud Service but that is not where I am going. The answer to our problem was in a service we have dabbled with before which is Amazon’s RDS (relational database service) service along with an EC2 (elastic compute) server running ORDS. I have never really been completely happy recommending this approach for the RAD (REST, APEX, Database) stack before, two things have changed to make this a much more appealing approach.

• You can now perform a full ORDS install on an RDS instance. This is thanks to new functionality in ORDS 19.1 which allows you to run the install as a user other than SYS. This is especially important now that REST services are no longer available in APEX alone.
• AWS have started making the latest version of ORDS available on RDS much sooner than they used to. For example, as of right now, September 2019, you can run ORDS 19.2 and APEX 19.1 on RDS, whereas Exadata Express is running ORDS 3 (ouch) and APEX 18.2.

​The diagram below shows the architecture we are using at JMJ cloud.

Here is a cost breakdown for the above architecture.

There is no perfect solution and there are certainly some things you should consider when moving to AWS.

• Although the database is almost fully managed by AWS, you are on your own with regard to the ORDS server. You need to standup an EC2 server and install ORDS, Tomcat, setup your load balancer etc. This means it is not as hands off as Autonomous Database Cloud Service.
• At time of writing, there is no way to install APEX PSE Bundle Patches to an RDS instance. Depending on the bug, this could be a big deal.
• For license included versions of the Oracle Database, Standard Edition 2 is way more affordable than Enterprise Edition. If you have specific use cases that require Enterprise Edition features, then keep an eye on your costs.
• Although AWS takes care of patching the DB server, you will be responsible for patching the EC2 server and upgrading Java, Tomcat and ORDS.

​I am not saying RDS is a magic bullet that allows you to achieve the hands-off capabilities of the Autonomous Database Cloud Service at a fraction of the cost. If you have the budget and you want absolutely nothing to do with the underlying architecture, then Autonomous Database Cloud Service is for you. If you want lower level access to the database (and / or ORDS) but want to be absolutely sure the database itself is being backed up and patched for you then it offers a great alternative. If you throw in easy access to other AWS services, this soon becomes a very compelling solution.
 
After all, for developers, the health and welfare of the database is really the scariest part of running your own RAD stack. If AWS is essentially taking care of this for you, the rest is reasonably straight forward.

The APEX_DATA_PARSER PL/SQL package was introduced in APEX 19.1. It comes from a long line of useful APIs produced by the APEX development team. APEX_DATA_PARSER solves a number of challenges with loading file based data that I have faced throughout my Oracle career of 25 years. In this post I will explain what problems it solves and how you can use it to make importing data from files much easier.

Almost all projects whether they are custom APEX applications or full-blown ERP implementations require that you import data into a database table from a file. As Oracle developers our peak productivity zone begins when we can handle data using SQL. The quicker we can start handling data using SQL, the quicker development can be completed.
 
APEX_DATA_PARSER provides APIs to help us to:

• Analyze a file to understand its format and structure.
• Identify the field names and datatypes of fields contained in the file.
• Use a SELECT statement to pull data from a file without having to develop complex parsing logic.

 
APEX_DATA_PARSER supports the following file formats:

• CSV
• JSON
• Excel (XLSX)
• XML

Previously, we have had to employ one of the following methods to get external file data into a database table to start handling the data using SQL:

• Use SQL*Loader to load data into a staging table and then kick off a PL/SQL process to perform transformations.
• Use UTL_FILE within PL/SQL to read data from files on the file system.
• Use ‘External Table’ functionality within PL/SQL to read data from files as if they were a table.
• Build a custom program using a language like Java to read data from the file, parse it and insert it into a table using JDBC.

 
All of these methods come with challenges, which include:

• None of them support parsing and loading JSON, Excel or XML files.
• None of these methods easily handle the structure of your file changing.
• All of these methods require that you copy the file to a specific server that the database is able to access.
• SQL*Loader and External Tables require that you manually build a definition file to define the structure of the file you are loading.
• UTL_FILE and External Tables require that you configure database directories to define the location of the external file on the file system.
• UTL_FILE requires that you manually build the parsing logic to split records into fields etc.

APEX_DATA_PARSER handles all of the challenges I described above (and more). It contains a series of APIs that allow you to discover the structure of a file and then parse the data in the file directly from a SQL statement.

The File
In order or for APEX_DATA_PARSER to act on your file, you first have to get it into a BLOB. You might now be thinking if I have to get the file into a BLOB then this really isn’t solving the problem of having to move the file to a location where the database is aware of it. Enter APEX_WEB_SERVICE. You could use APEX_WEB_SERVICE to first get the file BLOB from somewhere like AWS S3 and then consume the response using APEX_DATA_PARSER e.g.

​Discovery
One of the most interesting features for me is the ability to analyze a file and generate a JSON document containing everything you need to know about the file. 

APEX_DATA_PARSER.DISCOVER returns a JSON document with details of the file encoding, delimiters, and the names and data types for each of the fields in the file. This information is extremely useful. For example, you can use it to write code to handle changes to the numbers of columns in files that are uploaded. Today your user uploads a file with 10 columns and tomorrow they add a column. Being able to inspect the format of the file allows you to dynamically handle that 11th column. This is something you could never do with other Oracle loader tools.
 
To make things even easier, there is a companion API to APEX_DATA_PARSER.DISCOVER called APEX_DATA_PARSER.GET_COLUMNS.

​APEX_DATA_PARSER.GET_COLUMNS produces a PL/SQL array of the columns from the JSON profile. 

​Parsing
Now that we know what our file looks like we would like to parse and process the data within the file using SQL. This can be done using APEX_DATA_PARSER.PARSE.

​APEX_DATA_PARSER.PARSE returns a PL/SQL array with 301 columns, the line_number from the file and up to 300 columns of data from the file.

​Although the column names are generic (col001, col002 etc.) we know exactly what is in each column because we have the profile generated earlier.
 
At every turn, APEX_DATA_PARSER is making it easier for you to deal with information about the file and the file itself using SQL.

​There a few considerations that are worth noting.

Many More Parameters
There are many more parameters to the APIs I have described above. I encourage you to read the documentation to learn about these.
 
It Supports a Flat File Structure

• Although it supports JSON and XML file types, APEX_DATA_PARSER can only handle data in a flat structure.
• You cannot, therefore, parse out data from nested objects in JSON or XML.
• During discovery of JSON files, APEX_DATA_PARSER will try and determine the main array and parse the top level of that array only.

 
Basic DATE Type Identification

• It seems to rely on clues in the formatting of date fields to identify the format string.
• For example:
  • DD/MM/YYYY is not identified as a DATE while DD.MM.YYYY is.
  • MM/DD/YYYY is identified as a DATE.
• It seems to me if it tested a number of different date format strings against records in the file, it could improve its success rate in identifying DATE fields.

 
Performance
APEX_DATA_PARSER relies on underlying code to handle the actual data parsing. For example, if you are on 11g of the database it uses APEX_JSON to parse JSON. If you are on 18c of the database it will use the native JSON parser. The native JSON parser in 18c is orders of magnitude faster than APEX_JSON. In view of this the parsing speed can vary dramatically depending on the version of the DB you are on.
 
This is actually very clever. By encapsulating file parsing logic within APEX_DATA_PARSER you will automatically gain performance improvements when you upgrade from 11g to 18c.

​We have already used APEX_DATA_PARSER for loading payroll data from external payroll providers into Oracle EBS. These payroll files have a varying number of columns depending on the different pay elements in the payroll file (e.g. gross pay, sick pay, holiday, vacation, etc.). Being able to analyze the profile produced by APEX_DATA_PARSER at run time allowed us to pick up columns added to the file dynamically. As long as these columns matched Oracle payment elements, we could then load them right into Oracle.
 
Prior to APEX_DATA_PARSER this would have been nearly impossible. At the very least APEX_DATA_PARSER cut the development time in half.

For on-premise and cloud-based ERP projects, a significant portion of the technical budget is spent converting data (e.g. items, customers, suppliers, orders, invoices etc.) from a legacy system to a new Oracle ERP. APEX_DATA_PARSER can significantly reduce the cost and complexity of these conversions by removing the complexity of getting you to the stage where you can work with the file data in SQL.
 
There are many more examples where APEX_DATA_PARSER can save you time and allow you to provide real business benefit. For example, you can save users from manual data entry by allowing them to upload data from an Excel spreadsheet. APEX_DATA_PARSER even handles fetching data from a specific tab of a spreadsheet.
 
Here is the link to the APEX 19.1 documentation for APEX_DATA_PARSER

​In this post I will demonstrate a useful feature of APEX that does not get a lot of attention. ‘User Preferences’. It’s another one of those APEX features that saves you time. This allows you to focus more time on building important business logic that adds value to your business.

APEX User Preferences allow you to store name value pairs by user and APEX application. These name value pairs are persisted using an internal APEX table. You can set, get and delete user preferences declaratively or via APEX provided APIs.
 
You can imagine these name value pairs are stored as follows:

There are many reasons why you may want to store values related to a particular user:

• Default Time zone
• Default language
• Notification preference
• Default home screen
• Default unit of measure
• URL to profile picture
• etc.

​For the remainder of this post, I will demonstrate how to get and set preferences using a simple example. In this example, we have a user preference which determines if the user sees a Detailed or Summarized dashboard view when the user logs in.
 
Here is a screen shot of the a sample preferences page which I created where the user can set their default dashboard view.

​There is no need to formally create a user preference before setting a value for it. When you set a user preference for the first time, APEX creates a record in an internal table to store the preference and its value. This value is stored until you either change or delete the preference.
 
There are two ways to set a preference value.
 
1. Declaratively in a Page Process
One of the great things about user preferences is that you can set a user preference declaratively (no code) using a page process. The below page process is set to run when the user clicks the ‘Apply’ button (from our example above). When it runs, the user preference ‘DASHBOARD_VIEW’ is set to the current value of the page item ‘P3_DASHBOARD_VIEW’. 

​The key here is the type ‘User Preferences’ (under the ‘Identification’ section).
 
The type under the ‘Settings’ section has three options, two of which (in bold) can be used to set a user preference. Their names are pretty self-explanatory.

• Reset Preferences (remove all preferences for current user)
• Set to Item Value
• Set to Item Value if Not Null

More about the first option later in the post.
 
2. Programmatically Using an API
APEX also provides and API which you can use to set User Preferences. In the below code snippet, we are setting the user preference ‘DASHBOARD_VIEW’ to the value ‘DETAILED’ for the currently logged in user.

​There are also two ways to get a preference value.
 
1. Declaratively Using Page Item Source
You can declare the source for a page item to be a User Preference (again no code). This means that when the page is rendered, the page item automatically picks up the value for the preference.

​In our example, we need to initialize the item ‘P3_DASHBOARD_VIEW’ with the current value for the user preference ‘DASHBOARD_VIEW’.
 
2. Programmatically Using an API
APEX also provides and API which you can use to get the value for a user preference. In the below code snippet, we are getting the current value for user preference ‘DASHBOARD_VIEW’ for the currently logged in user.

​It may not be completely necessary to purge user preferences but it may be useful when you are de-activating users who no longer work for your company. There are three options for purging user preferences.
 
1. Declaratively Using a Page Process
With this option you can purge all current user preference entries for a particular user and APEX Application. When the below page process runs, it will purge all user preferences for the current user and APEX application. 

​In the page process above, we again have Identification > Type set to ‘User Preferences’. This time, we have the Settings > Type set to ‘Reset Preferences (remove all preferences for current user)’.
 
2. Via Workspace Administration
As a workspace administrator, you can view and purge user preferences for the currently logged in user or a specified user. You can access these options using the following navigation from Workspace Administration: Manage Service > Session State > Manage Preferences

​The screen shot below (from the ‘Purge Preferences by User’ option) shows preferences for user ‘JDIXON’. Clicking ‘Purge User Preferences’ will purge all preferences for this user.

​Finally, there is an API you can call to purge a single user preference for a given user in the current application. The following code will delete the preference ‘DASHBOARD_VIEW’ for the currently logged in user in the current application.

Without User Preferences, you would have to build a table and PL/SQL package to manage this kind of data in your APEX applications. The fact that this feature is baked into APEX saves you time and lets you focus on value-add features of your Application.
 
Here is the link to the APEX 19.1 documentation on user preferences: