Rest Enabling the Interactive Grid - Part 2

by Andreea Dobre, Oracle Romania in Bucharest

In the first Web Source Modules and the Interactive Grid blog post, Interactive Grid was built on top of a Web Source Module by using customized PL/SQL code to process two DML operations: GET and POST. The blog posting covered the following steps:

• REST enabling of the EMP table using ORDS
• Creating Web Source Module using the REST endpoint
• Customize the GET and POST operations in order to retrieve data and insert new records within the table
• Create  PL/SQL process to execute the Interactive Grid INSERT operation on the REST Service using the APEX_EXEC PL/SQL package.
• Use the proper validations for several columns to check, whether correct user input is provided.

However, a lot of functionality was missing. For instance, UPDATE and DELETE functionality was not present; the Interactive Grid also did not fetch the data from the REST Service on page load. This second part of the blog posting will close this gap:

• The Interactive Grid will retrieve its data from the REST Service. Since Interactive Grids cannot consume REST services out-of-the-box today, we'll use a Pipelined Table Function for this.
• The Interactive Grid will also be able to execute PUT and DELETE requests on the REST service. At the end we can use all three DML operations for the REST service: INSERT, UPDATE and DELETE.

Prerequisites

If you did not already read the first part of the blog posting, do so now and make sure to perform at least the following tasks:

1. REST Enable the database schema
2. Create a REST Endpoint for a table ("REST Enable" the table)
3. Create an APEX application and the Web Source Module 

Navigate to your application, then to Shared Components and Web Source Modules. You should see the Web Source Module from the first part of the blog posting.

The POST DML operation was already covered in the first part of the blog posting. Let's now apply the required changes for PUT and DELETE.

Changes on the PUT operation (UPDATE)

Edit the Web Source Module, then open the Operations tab and click the pencil to edit the PUT operation. Use the following JSON template as the Request Body Template:

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

Click Apply Changes to save your changes, then click the pencil corresponding to the PUT operation again. Now click the Add Parameter button. Create the following new Web Source operation parameters and use Request / Response Body as the parameter Type and keep the Direction as In.

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

Then create one 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.

The last parameter for the PUT operation will be a URL Pattern variable parameter named id.

Once finished, you should see 9 web source operation parameters, as follows. Click the Apply Changes button, when done.

Changes to the DELETE operation

For the DELETE operation the Request Body Template must remain  empty, and we need only one web source operation parameter: Create a parameter of type URL Pattern Variable, named id.

The definition of the DELETE Web Source operation should look as follows. Click the Apply Changes button, when done.

The list of Web Source Operations should look like the following screenshot.  Now all Web Source operations are configured. 

Create a pipelined table function

To have a fully REST-Enabled Interactive Grid, the grid must also show data from the REST service when the page loads. However, Interactive Grid only allows to specify a SQL Query to get the data from. So we must load the data from the REST Service, with a SQL query.

The new APEX_EXEC PL/SQL package - in combination with a pipelined table function will do the job for us. Run the below code in SQL Workshop to install the table function and the two object types it needs to execute. The table function will use the Web Source module you have created - similar to an APEX component. The table function code allows to apply arbitrary processing on the data received from the REST Service. However, in this example. the function will just return the data. 

Navigate to SQL Workshop and run the following SQL statements (either individually or upload them as a SQL script). 

create or replace type type_emp_t as object(
    empno    number(4),
    ename    varchar2(10),
    job      varchar2(9),
    mgr      number(4),
    hiredate date,
    sal      number(7,2),
    comm     number(7,2),
    deptno   number(2,0) )
/

create or replace type type_emp_tbl is table of type_emp_t
/

create or replace function f_get_web_source_content return type_emp_tbl pipelined 
is
    l_columns apex_exec.t_columns;
    l_context apex_exec.t_context;

    type t_column_position is table of pls_integer index by varchar2(32767);
    l_column_position t_column_position;
begin
    -- specify columns to select from the web source module
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'EMPNO' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'ENAME' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'JOB' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'MGR' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'HIREDATE' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'SAL' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'COMM' );
    apex_exec.add_column( 
        p_columns       => l_columns,
        p_column_name   => 'DEPTNO' );

    -- invoke Web Source Module and select data
    l_context := apex_exec.open_web_source_query(
        p_module_static_id => 'WebSourceModule_on_EMP',
        p_columns          => l_columns );

    -- now get result set positions for the selected columns
    l_column_position( 'EMPNO' )  := apex_exec.get_column_position( l_context, 'EMPNO' );
    l_column_position( 'ENAME' )  := apex_exec.get_column_position( l_context, 'ENAME' );
    l_column_position( 'JOB' )    := apex_exec.get_column_position( l_context, 'JOB' );
    l_column_position( 'MGR' )    := apex_exec.get_column_position( l_context, 'MGR' );
    l_column_position('HIREDATE') := apex_exec.get_column_position( l_context, 'HIREDATE' );
    l_column_position( 'SAL' )    := apex_exec.get_column_position( l_context, 'SAL' );
    l_column_position( 'COMM' )   := apex_exec.get_column_position( l_context, 'COMM' );
    l_column_position( 'DEPTNO' ) := apex_exec.get_column_position( l_context, 'DEPTNO' );

    while apex_exec.next_row(l_context) loop

        pipe row(
            type_emp_t(
                apex_exec.get_varchar2( l_context, l_column_position( 'EMPNO' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'ENAME' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'JOB' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'MGR' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'HIREDATE' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'SAL' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'COMM' )), 
                apex_exec.get_varchar2( l_context, l_column_position( 'DEPTNO' ))));

    end loop;

    -- finally: release all resources
    apex_exec.close( l_context );
exception
    when others then
        -- IMPORTANT: also release all resources, when an exception occcurs!
        apex_exec.close( l_context );
        raise;
end f_get_web_source_content;

Create the Interactive Grid region

Now navigate back to the Application Builder, select your application and open page designer for the page with the Interactive Grid. If you don't have a page with an Interactive Grid yet, create a new blank page and add an Interactive Grid region. Use the following SQL Query as the Interactive Grid region source:

select * from table(f_get_web_source_content)

Create the Interactive Grid DML Process

Now we need to change the Page Process (which saves Interactive Grid changes to the underlying data source). By default, Interactive Grid executes a SQL DML operation on the Region Source SQL Query. We will use custom PL/SQL code invoking the POST, PUT or DELETE REST methods. 

First, make sure, that Editing is enabled for your Interactive Grid. Then navigate to the page processing section and look up the Interactive Grid - Automatic Row Processing process. For this process, change the Target Type to PL/SQL Code. Turn off Lost Update Detection and set Lock Row to No.

Then add the following PL/SQL code. This code handles all INSERT, UPDATE and DELETE DML and will execute the POST, PUT and DELETE Web Source Operations accordingly.

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 case there is an update or delete operation executed on the Interactive Grid, then we will need to select 
       -- beforehand the row and save it through the following parameter:     
    if :APEX$ROW_STATUS in ('U', 'D') then
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'id', p_value => :EMPNO );
    end if;     
     
        -- in this case we take into account the INSERT operation therefore the condition will be based on :APEX$ROW_STATUS = 'C' and also the UPDATE operation that
        -- will be checked through :APEX$ROW_STATUS = 'U'
        -- APEX_EXEC.ADD_PARAMETER procedure adds an SQL parameter to the parameter collection        
     if :APEX$ROW_STATUS in ('C','U') 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
        -- based on the selection you make beforehand, the WebSource will execute one of the following operations:
    apex_exec.execute_web_source(
        p_module_static_id => 'WebSourceModule_on_EMP',
        p_operation        => case :APEX$ROW_STATUS 
                                  when 'C' then 'POST' -- INSERT
                                  when 'U' then 'PUT'  -- UPDATE
                                  when 'D' then 'DELETE' -- DELETE
                                   end,
        p_parameters       => l_parameters );   
end;

When done, save and run your page. The Interactive Grid fetches the data from the REST service - using the f_get_web_source_content table function.

You can change values, add and delete rows. When the Save button is clicked, the PL/SQL Interactive Grid process will invoke the required REST service methods.

After Delete

Summary

We now have completely REST enabled an Interactive Grid region - the powerful editing capabilities of Interactive Grid can now also be leveraged for REST Services. Like reports, charts or calendar regions, Interactive Grid regions can also access external data sources - APEX applications are no longer bound to the local database.

When thinking about doing this with your own (probably non-ORDS) REST Services, make sure that you have an understanding on how DML operations work with your REST Services:

• Are PUT, POST and DELETE handlers present
• How does the Request Body have to look like?
• Which additional parameters are required?

Once the details of a REST service are completely understood, the Web Source Module configuration and the code snippets described above can be changed accordingly.  Of course, there are also limitations: The table function to fetch data from the REST service always fetches all data. Be careful if the REST service returns large amounts of data. Also APEX does not allow to pass Interactive Grid filters to an own table function today.

However, for small and medium data sets, this might be nice approach to provide an editable grid on external data from a REST service. All custom PL/SQL code does not contain any low-level JSON parsing or HTTP interaction - APEX Web Source modules take care of that. The developer can focus on higher-level functionality.