The Oracle APEX blog is your source for APEX news, technical tips and strategic direction
-
Application Express
January 18, 2019
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:
- REST Enable the database schema
- Create a REST Endpoint for a table ("REST Enable" the table)
- 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.