Jon’s Blog

WITH FUNCTION using REST for an APEX List #JoelKallmanDay

With the introduction of REST Data Sources in APEX, the ability to use REST end points to create a fully digital experience over any underlying technologies has become increasingly straightforward.  APEX is no longer fully tied to underlying database tables to support its applications but provide a low code layer over any other solution that provides REST APIs making it highly flexible.

The REST Data Sources definitions can be used in multiple different types of components within APEX, from reports, forms, LOVs, charts, but one area that has still not been opened up to REST is Lists.  A list can either be Static, where a pre-defined list is defined, or Dynamic, where it runs a SQL query to define the list at runtime.  In the latest versions it is unable to get data from a REST Data Source. Or can it?

Due to the limitations of Lists then the solution must lie within a SQL statement.  So what options are there:

  1. Use a page process to populate a collection using PL/SQL and then select from APEX_COLLECTIONS


  3. WITH FUNCTION clause in SQL

Option 1

This option is fairly straightforward so I won’t cover it in detail but the page will trigger a package that should initiate a call to a web service and use the returned data to populate an APEX Collection.  The List will then select from APEX_COLLECTIONS.  A two stage process that is quite clunky and needs some maintenance.

Option 2

This option uses the concept that user APEX_WEB_SERVICE,MAKE_REQUEST_B will return a JSON document from a web service and then it uses APEX_DATA_PARSER.PARSE to render out the returned data in columns.

select * 
from   table
         ( apex_data_parser.parse
           ( p_content => apex_web_service.make_rest_request_b
                            ( p_url => <REST Service URL> ,
                              p_http_method => 'GET',
                              p_username => <username>,
                              p_password => <password,
                              p_file_name => 'json.js',
                              p_add_headers_row => 'Y' )

This will render out the data from the JSON using fields LINE_NUMBER, COL001..300, CLOB01..20, ROW_INFO. This has two major disadvantages:

  1. Knowing which column to use in the select statement for the List, i.e. are the fields in the JSON document always going to be in the same order

  2. Using static credentials. In the example above I have used Basic Authentication but I could use APEX Web Credentials using p_credential_static_id rather than username and password.  However, what happens when you want to use credentials specific to your user’s current session or you need to add additional parameters.

The function, APEX_WEB_SERVICE.MAKE_REST_REQUEST_B, does support the addition of parameter and value pairs but these use types that are not available in SQL.

Option 3

Back in Oracle Database release 12.1, Oracle release a new feature where it was possible to create a PLSQL function in a WITH statement in SQL. By defining the Function within a WITH clause the context switching between SQL and PLSQL is reduced. But up until now I have never had a use case.

The WITH clause in SQL allows developers to predefine data sets and then select from the returned values. Normally this is by defining other SELECT statements. In this case it provided the opportunity to return a JSON document and then select from the returned data set.

The function itself uses the APEX_WEB_SERVICE.MAKE_REST_REQUEST package but by using the WITH FUNCTION allows request headers to be defined prior to making the call.  In the case below the <authorization token> could substitute the current user session variables, such as a JWT Token.   Additionally, the parameters for the call for MAKE_REST_REQUEST for p_parm_name and p_parm_value that use the type, apex_application_global.VC_ARR2, can be set.  This type is not available in normal SQL.

The call will return the JSON payload and then using JSON_TABLE the values can be parsed and output as required, i.e. as a simple List clause.

    function get_ws return clob is
        l_response  clob;
        apex_web_service.g_request_headers(1).name := 'Content-Type';
        apex_web_service.g_request_headers(1).value := 'application/json';
        apex_web_service.g_request_headers(2).name := 'Authorization';
        apex_web_service.g_request_headers(2).value := <authorization token>;
        l_response := apex_web_service.make_rest_request
          ( p_url => <REST url>
            p_http_method => 'GET' );
        return l_response;
select null as lvl, as label,
       null as target,
       'NO' as is_current,
       null as image,
       null as image_attrib,
       null as image_alt
from   dual,
                 sequence       number      path '$.Sequence',
                 name           varchar2    path '$.Name'
                 )) x
order by x.sequence;


Follow My Blog

Get new content delivered directly to your inbox.

Create your website with
Get started
%d bloggers like this: