X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

  • October 12, 2018

Web Source Modules and the Interactive Grid: Part 1

Carsten Czarski
Consulting Member of technical Staff

by Andreea Dobre, Oracle Romania in Bucharest

With Application Express 18.1, APEX introduced support for using REST Services directly in APEX components: Using Web Source Modules, reports, charts or calendar regions can work directly on top of a REST service - with no manual coding required. However, only „read only“ components support REST Services, thus we can create an Interactive Report or a Chart page on top of a REST service, but not a form page or an Interactive Grid. 

The good news is: With a Web Source Module and some custom PL/SQL code, using the APEX_EXEC package, you can REST-enable your Interactive Grid based on a REST Service today. The INSERT, UPDATE or DELETE operations of the interactive Grid will map to the POST, PUT and DELETE operations of a REST service.

This blog posting, which is the first part of a series on REST-Enabling the Interactive Grid, will show how this works. The well-known EMP table and a REST service on top of it will serve as an example. This first part will show ...

  • how to create a Web Source Module on top of a REST service
  • how to create an interactive grid (on a local table first)
  • how to have the interactive grid invoke the REST service for SQL INSERT operations

Following blog postings will show how to complete that scenario, i.e. support other DML operations and have Interactive Grid actually show data from the REST service.

Prerequisites

In order to have a proper environment where to start playing with REST Services, go to apex.oracle.com and sign up for a free workspace. Navigate to SQL Workshop > RESTful Services and make sure to check ORDS Based RESTful Services.



The environment is now ready to go. First we need to REST-Enable the database schema, which is assigned to the APEX workspace. This can be done in SQL Workshop > RESTful Services > Register Schema with ORDS. As an alternative, you can do that with the following SQL statements in SQL Workshop > SQL Commands.

begin
    ords.enable_schema;
end;

Next, we need to create a REST Endpoint for a table. The easiest way to achieve that is to simply „REST-Enable“ the table as follows. REST-Enabling a table will create GET, POST, PUT and DELETE endpoints.

begin
    ords.enable_object( p_object => 'EMP' );
end;

Then navigate to SQL Workshop > Restful Services and check if the EMP table was enabled within the schema.

Use http://{server}/ords/{schema-alias}/emp/ (adjust the URL to your environment and workspace) to check the response. You should have some data displayed.

Create Application and Web Source Module

Create a new, empty application using the Create Application Wizard or navigate to an existing one. Within your application, navigate to Shared Components and look up Web Source Modules in the Data Sources category. Click on Create Button in order to create a new Web Source Module from Scratch. On the next step, choose Oracle REST Data Services as the Web Source Type, provide a name and the URL from above as the URL Endpoint. Click Next.

Keep the default settings for Remote Server on the next step and click Next

For our exercise we set the Authentication Required to No and click on Discover

You will see the data preview for REST Service. Click on Create Web Source.

Once the Web Source Module is created, you will be redirected to the overview page on existing Web Source Modules. We now need to make a few changes in the Web Source Module we just have created. Click on the module name.

Click the Advanced tab and set the Static ID to WebSourceModule_on_EMP, as we will need it when calling the Web Source Module with PL/SQL later on.

Open the Operations tab and click the pencil to edit the POST operation. Use the following JSON template as the Request Body Template:

{"empno":#EMPNO#,"ename":"#ENAME#","job":"#JOB#","hiredate":"#HIREDATE#","sal":#SAL#,"comm":#COMM#,"deptno":#DEPTNO#,"mgr":#MGR#}

Click Apply Changes, then click the pencil corresponding to the POST operation again and on Add Parameter. Create the following parameters and use Request / Response Body as the parameter Type and keep the Direction as In.

  • EMPNO
  • ENAME
  • JOB
  • HIREDATE
  • SAL
  • COMM
  • MGR
  • DEPTNO

Then create an additional parameter named Content-Type, use application/json as its Value and HTTP Header as Type. Set the Static switch to No for this parameter. Once finished, you should have 9 parameters as follows. Finally click the Apply Changes button.

Create the Interactive Grid

After creating the Web Source Module, you'll now use Page Designer to create a new Interactive Grid region. Navigate to Page Designer for an existing page or create a new empty page. Then drag a region of the Interactive Grid type to the page, name it WSM Interactive Grid and use select * from emp as the region source query.

Then, head over to the region attributes, enable Editing and, for now, only allow to Add Row.

Replace Standard DML processes with custom PL/SQL code

Since you have created the interactive grid region on a query selecting the EMP table, any changes would be written back to the EMP table as well. But we want to write our changes "to the web source module".

So we need to replace the standard interactive grid DML functionality with our own PL/SQL code. This code will use the APEX_EXEC PL/SQL package in order to invoke the Web Source Module. Note that APEX_EXEC package will encapsulate all data processing and querying capabilities. We do not need to use JSON parsing or make request calls anymore. Navigate to the Page Processes of your page and look up the Interactive Grid - Automatic Row Processing (DML) process. 

Navigate to the process attributes and change the Target Type attribute to PL/SQL and provide the following PL/SQL code.

declare
    l_parameters apex_exec.t_parameters;
    l_dateval date;
begin

        -- how to handle nulls on HIREDATE
     if :HIREDATE is null then
         l_dateval := sysdate;
     else
         l_dateval := to_date( :HIREDATE );
     end if;
     
        -- in this case we take into account only the INSERT operation therefore the condition will be based on :APEX$ROW_STATUS = 'C'
        -- APEX_EXEC.ADD_PARAMETER procedure adds an SQL parameter to the parameter collection
     if :APEX$ROW_STATUS = 'C' then 
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'ENAME',    p_value => :ENAME );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'EMPNO',    p_value => :EMPNO );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'JOB',      p_value => :JOB );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'HIREDATE', p_value => to_char( l_dateval, 'YYYY-MM-DD' ) || 'T00:00:00Z' );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'SAL',      p_value => coalesce( :SAL, 'null' ) );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'COMM',     p_value => coalesce( :COMM, 'null' ) );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'MGR',      p_value => coalesce( :MGR, 'null' ) );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'DEPTNO',   p_value => coalesce( :DEPTNO, 'null' ) );
     end if;
         
        -- invoke Web Source Module for and select data
    apex_exec.execute_web_source(
        p_module_static_id => 'WebSourceModule_on_EMP',
        p_operation        => case :APEX$ROW_STATUS 
                                  when 'C' then 'POST'
                                   end,
        p_parameters       => l_parameters );
   
end;

Test the functionality

Now it's time to do a first test on the Interactive Grid. Save your changes and run the page. First, the Interactive Grid looks as follows:

Do a few changes and save the page ...

When saving the changes to the interactive grid, you should notice that the actual DML operation takes a bit longer than usual. This is because interactive grid does now do a REST service invocation instead of simply executing SQL DML on a local table. 

Adding validations to avoid error messages from the REST service

However, depending on the actual data being entered, you won't see successful changes, but error messages instead:

This can happen when the entered data violates constraints being in place. Within the Oracle Database, we would see a clear ORA error message telling us what went wrong. However, a REST infrastructure does not know about databases and ORA error messages - REST is all about HTTP. A REST service responds with a HTTP status code, which is 200 for a successful operation and between 400 and 599 for error situations. The meanings of HTTP status codes are very generic - they have been made for HTTP and the web, not for interacting with a table in a database.

So, when the REST POST operation results in an error (because of table constraints), the REST service will just respond with either HTTP-500 (Internal Server Error) or HTTP-400 (Bad Request). To avoid end users running into these error messages, we should add Validations to the Interactive Grid columns - in order to make sure that only valid data is being posted to the REST Service. In Page Designer, navigate to the Interactive Grid, open the Columns node in the navigation tree on the left and add Validations to the columns as follows:

The manager EMPNO, being inserted, is already present in the table

Create a validation on the MGR column, pick Rows Returned as the Validation type and use select 1 from emp where empno = :MGR as the SQL Query.

The Salary is a valid number and between 0 and 100000.

Create a validation on the SAL column, pick Rows Returned as the Validation type and use select 1 from dual where trunc(nvl(:SAL,0),2) < 100000 as the SQL Query.

Make sure that the DEPTNO value is existent in the DEPT table

Create a validation on the DEPTNO column, pick Rows Returned as the Validation type and use select 1 from dept where deptno = :DEPTNO as the SQL Query.

The Commission value is a valid number and between 0 and 100000.

Create a validation on the COMM column, pick Rows Returned as the Validation type and use select 1 from dual where trunc(nvl(:COMM,0),2) < 100000 as the SQL Query.

After these changes being applied, the interactive grid will not allow to post invalid values to the REST service any more. This concludes the first blog posting on REST-Enabling the Interactive Grid component. Subsequent postings will explain how to implement DELETE and UPDATE operations and how to have the interactive grid displaying data from the REST service as well.