This post is part of a series that starts with this post.

In the last post we covered how to get the JSON-data returned from a REST-call converted back over to be represented as the rows and columns we are used to work with in the database. We’ll now take a look at how paging works and how we work with it to get a view to read all the rows.

Let us first revisit how one reads one chunk of data after another. The default is to get 25 rows in each call. If we look at it with cURL again we can get the first 25 by just referencing the end-point for our service.

curl https://.../ords/rest_demo_schema/vw_rest_svc/ | python3 -m json.tool 

As usual the python part just makes it more readable. You can run just the cURL-command without it and look for the next-url. The next-url will look something like this.

{
    "rel": "next",
    "href": "https://.../ords/rest_demo_schema/vw_rest_svc/?offset=25"
}

If you take the href-value and use it in a cURL-call you get the 25 next rows and you get a new next-url that ends with offset=50. Offset tells the service how many rows from the beginning to skip over before starting to return data. Thus to get all the rows you have to loop and call the next-url until there is no more. If there is more data, the hasMore attribute in the JSON-response will have the value true and if not it has the value false. Thus, loop until hasMore is false and grab all the rows that gets returned in all the calls.

That is exactly what we’ll do with PL/SQL to let us replicate using REST for a database link. We will then have just a view that get’s the user the needed data without them having to even know that we’re using REST.

We start with the infrastructure piece. That is the part that reads the REST-view repeatedly until all rows has been read. we do this from the target schema ( and database) rest_demo_caller.

create or replace package resttest as
  type payload_r is record (rsp clob);
  type payload_l is table of payload_r;

  function page_data return payload_l pipelined;
end resttest;
/

We need a type we can return (pipe) to a SQL. In this simple case we have only a CLOB in the record that then is set up as a type that is a table. The function has no parameters in this example and it returns data pipelined so it can be the source in a SQL.

The implementation is pretty straightforward also.

create or replace package body resttest as
  function page_data return payload_l pipelined is

    payload payload_r;
  
    payload_json json_object_t;

    next_url     varchar2(255) := 
       'https://.../ords/rest_demo_schema/vw_rest_svc/';
  
    more_data    varchar2(5) := 'true';
  begin
    while more_data = 'true' loop
      payload.rsp := apex_web_service.make_rest_request
                     (
                       p_url => next_url
                     , p_http_method => 'GET'
                     );
    
      pipe row(payload);
    
      payload_json := new json_object_t(payload.rsp);
      next_url := treat(payload_json.get_array('links')
                  .get(3) as json_object_t).get_string('href');
    
      more_data := payload_json.get_string('hasMore');
    end loop;
  end page_data;
end resttest;
/

Even if it is straightforward, it may not be obvious what and why so let’s go through the different parts.

On line 4 the payload is declared as a record of the type we declared in the package spec. It will hold the json that is returned from the REST-call.

Line 6 declares payload_json which will be used to deal with thye json-response using json-functions.

Line 8 declares next_url that holds the URL we’ll send in the rest -call.

Line 11 more_data stores the value for hasMore from the last REST-call.

In the begin-end (12-29) everything is a loop on more_data checking if there is yet more data to be fetched.

Line 14 makes the actual REST-call lacing the returned JSON in payload.rsp.

Line 20 is where the record is piped out to the calling SQL.

Line 22 takes the JSON and puts it into a parsed JSON-object. It may look strange if you are new to object-notation syntax. But it creates a new object, nothing more.

Line 23 is a bit complex, but all it does is to navigate the JSON-document. It begins with grabbing the “links” array, in there it takes the fourth element (which is a record) and returns the value for “href”. In plain english it takes the value of the next-url and puts it into the next_url variable. Why does it take the fourth element? PL/SQL starts arrays at 1. True, but this is JSON so it stays true to JSON rather than PL/SQL.

Lastly on line 26 the attribute hasMore is pulled out to the variable more_data. This is so the loop ends when there is no more data to read from the rest-service.

Now we have a function that will return one JSON-document after another from the REST-service as long as we keep fetching them and there is more data to be had. To get this data converted to rows (each JSON-doc has 25 rows) we can use a SQL that looks like the one we used in the last post.

select object_id
     , owner
     , object_name
  from table(resttest.page_data) t
        cross join json_table (t.rsp, '$.items[*]'
                      columns ( object_id   number   path '$.object_id'
                              , owner       varchar2 path '$.owner'
                              , object_name varchar2 path '$.object_name')) j

This is more or less identical with the SQL in the last post with a couple of changes. The select in the beginning of the FROM-clause has been replaced with a call to the function we just defined. This is to keep getting more and more data. The other one is that the first parameter in json_table is now t.rsp. “t” is the same as it is the alias for the data from the REST-call. But rsp used to be response, it changed due to how the column in the record was named.

With this in place we can now create a view based on this sql to allow us to select the data we want using a plain SQL.

create view the_view as
select object_id
     , owner
     , object_name
  from table(resttest.page_data) t
        cross join json_table (t.rsp, '$.items[*]'
                      columns ( object_id   number   path '$.object_id'
                              , owner       varchar2 path '$.owner'
                              , object_name varchar2 path '$.object_name')) j
;

You can now give a user access the the view “the_view” and they can get the data using this simple SQL.

select * from the_view;

OBJECT_ID OWNER  OBJECT_NAME 
      134 SYS    ORA$BASE    
      143 SYS    DUAL        
      144 PUBLIC DUAL        
      441 PUBLIC MAP_OBJECT  

The user does not need to know that the data is from json/rest or anything. To them it is just data they can work with. Pretty neat.

In the next post we’ll take a look at query-parameters. It is a complex area and we will just scratch the surface. But we can use it for a lot of things even with our simple setup of a rest enabled view.