This blog was originally published on Mar 4, 2019.

The latest Application Express Release 19.1 takes its support for external data sources (REST Services and REST Enabled SQL) to the next level. Forms do now support REST services and REST Enabled SQL declaratively. Thus APEX allows to create report, chart and form pages on REST services – all declarative and without manual coding. This blog posting shows, how it works.

Prerequisites I: Create a table and load sample data

First, we need a REST service, to create the APEX application with its report and form pages on. For this blog posting, we’ll create one ourselves in the Application Express workspace itself. The following sections describe how to first create a table and load some sample data and then how to expose that table as a REST service. Of course, if you have an existing table, you can go with that as well.

Navigate to SQL Workshop > Utilities > Sample Datasets

You’ll see a list of available sample datasets. Click the Install button for Tasks Spreadsheet dataset.

The following dialog shows a few details about this sample data set. Click the Next button in the lower corner and then the Install Dataset button.

Prerequisites II: Expose a REST API on top of that table

Next, we’ll expose a REST API for that table. Navigate to SQL Workshop > RESTful Services.

You should see a message that your schema as not been registered with ORDS so far. Click the Register Schema with ORDS button.

Provide a Schema Alias, if you wish and determine whether to install an additional sample REST Service as well. Then click the Save Schema Attributes button in order to enable the database schema for REST services. Note: That will not expose any REST API, but enable the schema to do so.

Click on the Enabled Objects tree node on the left. Right now, no objects should be REST-Enabled, i.e. there should be no exposed REST APIs right now.

Then navigate to SQL Workshop > SQL Commands and execute the following tiny PL/SQL block:

begin
    ords.enable_object( 
        p_object       => 'SAMPLE$TASKS_SS',
        p_object_alias => 'sample-tasks' );
end;

As an alternative to executing this PL/SQL block, navigate to SQL Workshop > Object Browser, look up your table and click the REST tab.

Now, the list of Enabled Objects in SQL Workshop > RESTful Services should contain an entry for the SAMPLE$TASKS_SS table. That means, we have exposed a REST API for that table.

Prerequisites III: First tests with the generated REST API

You should be able to perform a first test on your REST API. Use your browser to invoke it with the following URL:

http://{server}/ords/{rest-schema-alias}/sample-tasks

Assumed that we’re working on the APEX Early Adopter Instance, and we used the Schema Alias myrestservices when we registered the schema with ORDS, then the URL would be as follows:

http://apexea.oracle.com/pls/apex/myrestservices/sample-tasks

Next, let’s test the REST endpoint for the POST method (to create a row). This cannot be done easily with the browser – so we’ll use the command line and the curl utility.

curl -X POST \
     -H'Content-Type:application/json' \
     -d'{"project":"APEX 19.1","task_name":"Evaluate Early Adopter","start_date":"2019-03-01T00:00:00Z","end_date":"2019-03-05T00:00:00Z","status":"Planned","assigned_to":"John Doe","cost":0,"budget":1000}' \
     http://{server}/ords/{rest-schema-alias}/sample-tasks/

You should see a JSON response similar to the following

{"id":171148131930643518608543600185293359013,"project":"APEX 19.1","task_name":"Evaluate Early Adopter",...

You might fetch the row again using a browser and the following link:

http://{server}/ords/{rest-schema-alias}/sample-tasks/171148131930643518608543600185293359013

If these operations work without errors, your REST service does work. Before moving on with building an APEX application, let’s try to create another row: This time we’ll try to insert a project name with more than 30 characters. However, the PROJECT column of the SAMPLE$TASKS_SS table is defined as VARCHAR2(30). So this request cannot succeed … 

curl -X POST \
     -v \
     -H'Content-Type:application/json' \
     -d'{"project":"APEX 19.1 and this project name is larger than 30 characters!","task_name":"Evaluate Early Adopter","start_date":"2019-03-01T00:00:00Z","end_date":"2019-03-05T00:00:00Z","status":"Planned","assigned_to":"John Doe","cost":0,"budget":1000}' \
     http://{server}/ords/{rest-schema-alias}/sample-tasks/

… and is does not succeed. It fails with HTTP Status code 500 and some HTML output. 

:
* upload completely sent off: 249 out of 249 bytes
< HTTP/1.1 500 Server Error
< Content-Type: text/html
< Error-Reason: error="resource.generator.evaluation"; error_description*=UTF-8''The%20request%20could%20not%20be%20processed%20because%20an%20error%20occurred%20whilst%20attempting%20to%20evaluate%20the%20SQL%20statement%20associated%20with%20this%20resource.%20Please%20check%20the%20SQL%20statement%20is%20correctly%20formed%20and%20executes%20without%20error.%20SQL%20Error%20Code%3a%20ORA-12899%3a%20Wert%20zu%20gro%c3%9f%20f%c3%bcr%20Spalte%20%22CCZARSKI%22.%22SAMPLE$TASKS_SS%22.%22PROJECT%22%20%28aktuell%3a%2061%2c%20maximal%3a%2030%29%0aORA-06512%3a%20in%20Zeile%204%0a%2c%20Error%20Message%3a%20%7b2%7d.
< Content-Length: 16273
<!DOCTYPE html>
<html>
<style type="text/css" media="screen">
footer,header{display:block;}

So, if a request to a REST API fails on the server side, we’ll get back an HTTP status code between 400 and 500. It depends on the web server (here: ORDS) configuration whether additional data is returned. Later in this blog posting, it will be important to keep this in mind.

Create a Web Source Module

Now create a new APEX application ( or use an existing one ), navigate to Shared Components > Web Source Modules.

Create a new Web Source Module from Scratch.

In the next wizard step, provide a name for the Web Source Module and the URL which also used when trying the REST service with your browser: http://{server}/ords/{rest-schema-alias}/sample-tasks

The next wizard steps allows to adjust the Base URL path to be stored as the Remote Server object. In most cases, you can simply accept the APEX proposal here. The Remote Server object stores the first, common part of the URL which might be used by multiple Web Source Modules.

Since the REST service does not require Authentication, simply click the Next button in the Authentication wizard step.

After clicking Discover, APEX will invoke the REST service URL. From the JSON response, APEX derives attributes and their data types and uses this to create a Data Profile. The final step of the wizard should look as follows.

Click the Create Web Source button. The dialog will close and meta data for this REST service will be stored as a new Web Source Module.

Now click the Web Source Module name to review its properties.

Navigate to the Data Profile section.

APEX nicely detected the attributes from the REST service JSON response. However, the CREATED and UPDATED columns were detected as VARCHAR2 columns, which is not correct. Let’s fix this right now. Click the yellow pencil for the CREATED column. Change the Data Type to Date and (since the JSON response contains values with time zone information), set the Has Time Zone attribute to Yes. Apply the same change to the UPDATED column as well.

Create Form and Report pages for the REST Service

Next, we’ll start to use this Web Source Module in the APEX Application. With just a few mouse clicks, we’ll create an overview report and a form to edit data on the REST service – without any manual PL/SQL or SQL coding.

Navigate to Application Builder and click Create Page.

Choose the Form section.

We want to create Report with Form pages.

On the next screen, configure the basic settings of your pages:

  • Choose the implementation you want to use: Interactive Report, Classic Report. Interactive Grid is not supported with REST services in APEX 19.1, so you cannot use Interactive Grid. Pick Interactive Report.
  • Provide a name for the report and form pages
  • Specify whether to have the form page created as a modal dialog or as a normal page

On the next dialog step, decide whether you want to have a Navigation Menu entry for your new pages. Then you’ll get to choosing the Data Source for your report and form pages.

  • Choose Web Source as the Data Source
  • Pick the Web Source Module you just have created from the select list
  • Move all columns in the Shuttle to the right (which is the default). This configures the columns to show in the report.

Click Next to configure the form page. As in the previous step, keep all columns, but designate the ID column as the Primary Key. It’s clear that APEX treats a Web Source like a table with rows and columns. And as for a table, we need a primary key to identify existing rows.

Finally click Create in order to create your pages. You will be redirected to Page Designer for the report page.

Run your page by clicking the Play symbol in the upper right corner of the screen. After logging in, you should see the contents of the REST service, nicely formatted as a tabular report.

The ID column is a bit disturbing. Let’s hide it.

Next, click the yellow pencil in one of the rows – you should be redirected to the Form page.

Change the Task Name value to Identify *the real* server requirements and klick the Apply Changes button in the lower right corner. The change will be sent to the REST service (as a PUT request) and you’ll be redirected to the report page again.

Tweaking the form page

Navigate back to the form. You’ll see a lot of form fields which are actually not needed.

These columns should not be visible on the form. Open Page Designer for that page and change the Type of these items to Hidden. Do not remove the items. Hidden items are present – the values are fetched and sent to the REST service with the PUT request. Removing the items completely means that no values for these columns will be sent to the REST service on update – which will lead to errors.

Let’s try another update now. On the report page, open the form for a row again and change the Project value to NEW!!! ACME Web Express Configuration. When you try to save this change, you’ll see an error message.

Remember the REST service error we have produced with the curl utility before …? This is exactly the same: the PROJECT column in the SAMPLE$TASKS_SS table is defined as VARCHAR2(30) … 

… and when we clicked the Apply Changes button in the form, APEX executed an HTTP PUT request to the REST endpoint for us. Oracle REST Data Services (ORDS) executed that PUT request and generated a SQL Update. That update failed, since NEW!!! ACME Web Express Configuration is 37 characters.

In the Oracle database world, we would get an ORA-12899: value too large for column error message. However, REST is not only Oracle, REST is generic: so there is no standard to communicate an ORA error back to the caller. REST services simply respond with standard HTTP error codes. In this case we get HTTP 500: Internal server error, which means “something went wrong”. Unfortunately, REST services typically do not provide more information.

So we need to harden the form page in order to avoid these messages – add length restrictions to the form items:

  • P5_PROJECT: 30
  • P5_TASK_NAME: 255
  • P5_STATUS: 30
  • P5_ASSIGNED_TO: 30

Also some attributes are required and cannot be empty. In Page Designer, set the Value Required property to Yes for the following items:

  • P5_PROJECT
  • P5_START_DATE
  • P5_STATUS

One that is done, run the form again. APEX now prevents the end user from entering invalid data. When working with REST services, it’s very important to have proper validations for all page items in place – since invalid data just leads to simple non-descriptive HTTP 500 or HTTP 400 messages.

Summary

Application Express 19.1 takes its support for REST Services to the next level – while the previous release focused on REST support for read only visualization components, APEX 19.1 adds data manipulation support and allows to create forms on a rest service. 

Using APEX, working with REST Services becomes as easy as with a table. Report and Form pages are simply created with a few mouse clicks – all functionality is declarative and thus easy manageable. In the next blog postings will continue to provide insights into this new functionality:

  • Forms on “non-ORDS”, custom external REST APIs
  • Advanced forms topics: Lost Update support, alternative DML logic, and so on
  • Using the data manipulation support for REST Services with PL/SQL

About REST and REST Enabled SQL support for read only visualization components, this blog already contains postings: