X

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

  • July 16, 2018

Creating a CRUD form on a REST Service with APEX 18.1

Carsten Czarski
Consulting Member of technical Staff

In this blog posting we will highlight the new REST capabilities of Application Express 18.1 once more. This time we will show how to create a form to insert, update or delete rows - but not from a table, the form will work on a REST service instead.

APEX 18.1 provides declarative support to build components on top of a REST service. However, only Read Only components like like reports or charts can use Web Source Modules directly; there is no wizard to create a form on a Web Source Module.

So we have to implement some custom PL/SQL code in order to build a DML form on a REST service. As this blog posting shows, Web Source Modules can still do a lot of the work for us: We will still be able to use the APEX_EXEC package - no manual JSON parsing will be required and we won't have to do any manual HTTP request.

Note that full declarative support for Read/Write components like Forms or the Interactive Grid is planned for one of the next releases of Application Express - when that is available, you will be able to create a Form on a REST service directly, without any manual work at all - as you can do today with a report.

Provide a REST Service: Using ORDS Auto-REST

First we need the REST Service to work on. For this blog posting we will use the well-known EMP table and the "Auto-REST" feature of Oracle REST Data Services. The most easy approach is to navigate to SQL Workshop > SQL Commands and to execute the following simple PL/SQL Block:

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

Then, to check whether the REST Service works, try to execute the REST Service using a browser or the "curl" command line utility. Use the URL: http://{ords-server}:{port}/ords/{schema}/emp/.  With a browser, the response should look as follows:

Create the APEX Web Source Module

Then, head over to Application Express, log into your workspace and create an application. Navigate to Shared Components and lookup Web Source Modules. In a new application, this section is empty. Click the Create button in order to create a new Web Source Module from Scratch.

Choose Oracle REST Data Services as the Web Source Type, provide the URL endpoint and click Next.

Just click the Next button in the following wizard step for the Remote Server. In the Authentication step, simply hit the Discover button. You should see the following screen, which indicates that APEX can talk to the REST service.

Click Create Web Source Module in order to save REST service meta data. You will be navigated back to the list of web source modules.

Create a report on top of the Web Source Module

Then create a classic report on that Web Source module, as described in the Application Express Early Adopter: REST Services! blog posting. You can also use page designer: Drag a classic report region onto your page and change the Source attributes in the property pane on the right to use the new Web Source Module.

Run the page. You should see the data from the EMP table - which actually comes from a REST service.

This was the read only part. Now we'll get to the read / write part of the story. 

Insert, Update or Delete on a REST Service

When we REST-Enabled the EMP table (with ORDS.ENABLE_OBJECT), not only a GET REST Service had been created. ORDS also provides REST Services to perform DML (insert, update or delete) rows.

  • INSERT:
    POST Request to http://{server}:{port}/ords/{schema}/emp/.
    The request body contains the data for the new row in JSON format.
  • UPDATE:
    PUT Request to http://{server}:{port}/ords/{schema}/emp/{empno}.
    The request body contains the data for the new row in JSON format.
  • DELETE:
    DELETE Request to http://{server}:{port}/ords/{schema}/emp/{empno}.
    The request body must be empty.

Let's first try that with the "curl" command line utility (which is always a good tool to test our understanding of a REST service). The following curl invocation creates a new row by executing a POST request.

curl -H"Content-Type:application/json" \
     -X POST \
     -d'{"empno":4711,"ename":"APEX","job":"DEV","hiredate":"2001-01-01T20:00:00Z","sal":10,"comm":null,"deptno":10,"mgr":7839}' \
     http://localhost:28080/ords/testit/emp/
 

You should see the following resonse, indicating that the operation was successful.

{"empno":4711,"ename":"APEX","job":"DEV","mgr":7839,"hiredate":"2001-01-01T20:00:00Z","sal":10,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://localhost:28080/ords/testit/emp/4711"},{"rel":"edit","href":"http://localhost:28080/ords/testit/emp/4711"},{"rel":"describedby","href":"http://localhost:28080/ords/testit/metadata-catalog/emp/item"},{"rel":"collection","href":"http://localhost:28080/ords/testit/emp/"}]}

We can also check by querying the table directly:

SQL> select * from emp;

  EMPNO ENAME    JOB            MGR HIREDATE                 SAL   COMM   DEPTNO
------- -------- ------------- ---- ---------------------- ----- ------ -------- 
   7369 SMITH    CLERK         7902 17.12.1980 00:00:00      800              20 
   7499 ALLEN    SALESMAN      7698 20.02.1981 00:00:00     1600    300       30 
   7521 WARD     SALESMAN      7698 22.02.1981 00:00:00     1250    500       30 
   7566 JONES    MANAGER       7839 02.04.1981 00:00:00     2975              20 
   7654 MARTIN   SALESMAN      7698 28.09.1981 00:00:00     1250   1400       30 
   7698 BLAKE    MANAGER       7839 01.05.1981 00:00:00     2850              30 
   7782 CLARK    MANAGER       7839 09.06.1981 00:00:00     2450              10 
   7788 SCOTT    ANALYST       7566 09.12.1982 00:00:00     3000              20 
   7839 KING     PRESIDENT          17.11.1981 00:00:00     5000              10 
   7844 TURNER   SALESMAN      7698 08.09.1981 00:00:00     1500      0       30 
   7876 ADAMS    CLERK         7788 12.01.1983 00:00:00     1100              20 
   7900 JAMES    CLERK         7698 03.12.1981 00:00:00      950              30 
   7902 FORD     ANALYST       7566 03.12.1981 00:00:00     3000              20 
   7934 MILLER   CLERK         7782 23.01.1982 00:00:00     1300              10 
   4711 APEX     DEV           7839 01.01.2001 12:00:00       10              10 

Since we now know, how to call the POST, PUT and DELETE REST Services, let's prepare the already existing Web Source Module and then build the CRUD form.

Prepare the Web Source Module for CRUD operations

Since APEX 18.1 does not provide declarative support for Forms on a Web Source Module, we must provide some information ourselves, but we can still leverage Web Source Modules and let APEX do a lot of low-level work for us.

Look up your new web source module in Shared Components. First, navigate to the Advanced tab and note down the exact Static ID value of the Web Source Module (here: Auto_REST_Service_EMP). We'll need that later on.

 

 

Then select the Operations tab.

We can see that APEX already did some work for us. Since this is an ORDS REST Service, APEX detected, that URL endpoints for POST, PUT and DELETE exist and it added these to the REST Service meta data.

Remove the unneeded DELETE operation

ORDS provides indeed two DELETE endpoints: One is to delete a single row, the other one to delete the whole collection (i.e. all rows from the table). We won't need the latter one in our APEX application. So click the yellow pencil of the DELETE operation which has no value in the Database Operation column and remove that operation by clicking the Delete button.

Changes to the POST operation

Then, click the pencil to edit the POST operation.

Provide 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 and then the pencil again to come back to this form. Then click the Add Parameter button to open the Operation Parameter dialog.

For each column name, which you have created a #PLACEHOLDER# for, add a Parameter of the Request / Response Body type.

When finished, you should have eight Operation Parameters, one for each column of the EMP table. Then add another parameter of Type HTTP Header with Content-Type as its name and application/json as the value. The Operation Parameters section for the POST operation should finally look as follows.

Changes to the PUT operation.

For the PUT operation, provide the following JSON Request Body template (it's similar to the template for the POST operation, but without the EMPNO column).

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

Then add Web Source Parameters as well, similar as done for the POST operations:

  • One Parameter of type Request / Response body for each #PLACEHOLDER# in the JSON Request Template
  • One static Parameter of type HTTP Header with Content-Type as the Name and application/json as the value
  • One Parameter of type URL Pattern variable with name id.

The Operation parameters section for the PUT operation should look as follows:

Changes to the DELETE operation

The remaining DELETE operation does not need any JSON Request template and only one parameter definition: Create a parameter of type URL pattern variable with name id.

Now, the operations are all set and the Web Source Module has been prepared. We can continue building the form page.

Create the form page

Next, create a new page for the form on the REST service. Create a blank page and add a region containing eight items (one for each form element) to that page. When finished, your form might looks as follows (it has no functionality so far).

Next, we'll add functionality to populate the form with values on page load. We'll need do this using custom PL/SQL code and the APEX_EXEC package. The blog posting  APEX 18.1 Early Adopter 2: REST Services and PL/SQL contains a good template for our form loading process. 

In Page Designer, navigate to the Before Headers section in the page tree on the left and create a new Process.

Name the process Load REST Data and provide the following PL/SQL Code:

declare
    l_columns apex_exec.t_columns;
    l_context apex_exec.t_context;
    l_filters apex_exec.t_filters;

    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 => 'HIREDATE');
    apex_exec.add_column( p_columns => l_columns, p_column_name => 'MGR');
    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');

    -- add a filter to query only the selected row
    apex_exec.add_filter(
        p_filters     => l_filters,
        p_column_name => 'EMPNO',
        p_filter_type => apex_exec.c_filter_eq,
        p_value       => to_number( :P2_EMPNO ) );

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

    -- now get result set positions for the selected columns
    for c in 1 .. l_columns.count loop
        l_column_position( l_columns( c ).name ) := apex_exec.get_column_position( l_context, l_columns( c ).name );
    end loop;

    -- if we have a result set, set the form items
    if apex_exec.next_row( l_context ) then
        :P2_ENAME    := apex_exec.get_varchar2( l_context, l_column_position( 'ENAME' ) );
        :P2_JOB      := apex_exec.get_varchar2( l_context, l_column_position( 'JOB' ) );
        :P2_HIREDATE := apex_exec.get_varchar2( l_context, l_column_position( 'HIREDATE' ) );
        :P2_SAL      := apex_exec.get_number  ( l_context, l_column_position( 'SAL' ) );
        :P2_COMM     := apex_exec.get_number  ( l_context, l_column_position( 'COMM' ) );
        :P2_MGR      := apex_exec.get_number  ( l_context, l_column_position( 'MGR' ) );
        :P2_DEPTNO   := apex_exec.get_number  ( l_context, l_column_position( 'DEPTNO' ) );
    -- otherwise raise NO_DATA_FOUND    
    else
        raise no_data_found;
    end if;

    -- 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;

Add a server-side condition to have the process only executed when the P2_EMPNO item IS NOT NULL, then save your changes.

Let's now test whether this PL/SQL code already works. Navigate back to the report page and change the EMPNO column of the report to be rendered as a link to the new form page. 

Run the report page - you should now see links in the EMPNO column.

Then add a button to the report page which allows to create a new row.

  • Button Name: CREATE_NEW
  • Button Label: Create New
  • Button Action: Redirect to Page
  • Target: Page 2 (the form page); set Clear Cache for page 2 (the form page)

When clicking the link in the EMPNO column, the form page should be loaded and populated with the values for the selected row; when clicking the Create New button, an empty form should be loaded.

This concludes the first part of the form page. We loaded data from the REST service into the page items without using any APEX_WEB_SERVICE call and without doing any manual JSON parsing.

Add DML processes to the form

Before adding the PL/SQL code to process POST, PUT and DELETE requests add the required buttons to the page:

  • CREATE with an IS NULL condition for the P2_ENAME item
  • APPLY_CHANGES with the IS NOT NULL condition for the P2_ENAME item
  • DELETE with the IS NOT NULL condition for the P2_ENAME item

The page should then look as follows:

Now let's add the PL/SQL Code to process the INSERT, UPDATE or DELETE operations. We have provided all required meta data in the Web Source Modules and we can thus work with the APEX_EXEC package alone. Dependent on the button clicked, the PL/SQL code will prepare parameters for the Web Source Module operation and finally call APEX_EXEC.PROCESS_WEB_SOURCE

In Page Designer, create a new PL/SQL process in the Processing section.

Name it Process REST DML and provide the following PL/SQL code:

declare
    l_parameters apex_exec.t_parameters;
begin

    -- add the primary key value as the "id" parameter; this will be appended to the URL for DELETE and APPLY_CHANGES
    if :REQUEST in ( 'DELETE', 'APPLY_CHANGES' ) then
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'id', p_value => :P2_EMPNO );
    end if;

    -- add form item values as parameters for CREATE and APPLY_CHANGES
    if :REQUEST in ( 'CREATE', 'APPLY_CHANGES' ) then
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'ENAME',    p_value => :P2_ENAME );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'EMPNO',    p_value => :P2_EMPNO );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'JOB',      p_value => :P2_JOB );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'HIREDATE', p_value => to_char( to_date( :P2_HIREDATE ), 'YYYY-MM-DD' ) || 'T00:00:00Z' );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'SAL',      p_value => :P2_SAL );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'COMM',     p_value => coalesce( :P2_COMM, 'null' ) );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'MGR',      p_value => :P2_MGR );
        apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'DEPTNO',   p_value => :P2_DEPTNO );
    end if;
        
        -- invoke Web Source Module for and select data
    apex_exec.execute_web_source(
        p_module_static_id => 'Auto_REST_Service_EMP',
        p_operation        => case :REQUEST 
                                  when 'APPLY_CHANGES' then 'PUT' 
                                  when 'CREATE'        then 'POST'
                                  when 'DELETE'        then 'DELETE' end,
        p_parameters       => l_parameters );
end;

As Server-Side condition, choose Request is contained in value and provide CREATE:APPLY_CHANGES:DELETE as the Value. That makes sure that the code is only called when one of the three buttons has been clicked. Finally provide a nice success message and add a branch back to the report page (here: page 1).

Now we can test the CRUD form:

  • Click the Create New button below the report, enter some data and save a new row

  • Update the Name value of that new row

Of course, this form can be further extended - think about validations, additional processes and dynamic Actions. The interesting bit is, that we're able to use Web Source Modules for a DML form, although APEX 18.1 does not provide declarative support so far. Using a bit of additional meta data and some custom PL/SQL code using the APEX_EXEC package we're able to build the form processes without using any manual HTTP or JSON handling code.