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

It is time to turn to query parameters. It expands on the end point to allow slicing and dicing what we get back. Or in other words it allows projection. Or in layman terms, you can supply a where-clause expressed in jSON. You can also define sorting and much more.

query parameters are basically key-value pairs with an equal sign as the separator. THis sounds much more academic and complex than it is in reality.

Our tests will all have the end-node as its base. That is for our test-case https://.../ords/rest_demo_schema/vw_rest_svc/. All query parameters are added after that.

Limit the number of rows

The default for number of rows returned is 25 as we have seen in most of the previous posts. But let’s change that for a single call and get just the first row. Add limit=1 to the url, We need to start with a question mark to indicate that the http-string now continues with parameters. I prefer to use cURL as it is as bare bones as one can get with http-calls. Remember the python module used for formatting so it get’s easier to read? All of that means that the call would look like this.

curl https://.../ords/rest_demo_schema/vw_rest_svc/\?limit=1| python3 -m json

Note that dues to Linux shell mechanics the question mark has special meanings so I have to escape it by putting a backslash before it. That is only needed on the prompt in Linux so remove it in the web browser or database.

Skip some rows

Next up, let’s skip three rows in the result set and get one. Run the REST-call without any parameters to see all rows so you can verify that the result is correct. Add \?offset=3\&limit=1 to the end node. Note that ampersand also has special meaning so on the prompt you need to escape it also. Look att the next and prev URLs, they are now using the limit you sent in so using them you get the same amount of data for every call you make using those.

Sorting

How about sorting data by the name of the objects? Just add $orderby to the end node. This one is a bit more complex as in has another key-value pair as its value. {$orderby:{object_name:ASC}} says that we want to sort by the object_name in ascending order. We’ve now gotten enough complexity with our command that we need to url-encode the parameter for curl to not interpret it and cause errors.

curl --get https://.../ords/rest_demo_schema/vw_rest_svc/ \
     --data-urlencode 'q={"$orderby":{"object_name":"ASC"}}' \
     | python3 -m json.tool

We specify that GET is the method to use and we request URL-encoding of the parameterstring. curl will replace special charaters with %-encoded values. It then concatenates that to the URL and puts a questionmark before it.

The result is 25 rows (as per the default) where the result is sorted in ascending order, thus the result are objects starting with the letter A.

Sorting + Limit + Offset

Taking the above in and combining them to allow limiting number of rows from a sorted result and skipping the first n rows.

We’re using both –data-encode and –data as the parameter values in –data does not have to be encoded. It cannot be padded onto the string we add to –data-urlencode as it will encode & to encoded values.

curl --get https://.../ords/rest_demo_schema/vw_rest_svc/ \
     --data-urlencode 'q={"$orderby":{"object_name":"ASC"}}' \
     --data '&limit=1&offset=3' | python3 -m json.tool

The result is the fourth row sorted by object_name.

Sorting + Condition + Limit

Next up is a similar variant, only now we want to define the start for the search with a where-clause like condition rather than skipping over a number of rows.

curl --get https://.../ords/rest_demo_schema/vw_rest_svc/ \
     --data-urlencode 'q={"$orderby":{"object_name":"ASC"},"object_name":{"$between":["AD","Z"]}}' \
     --data '&limit=1' | python3 -m json.tool

I use between here to achieve a greater than check. I have not found another filter-criteria that achieves that for strings. For number there is “column”:{“gt”:12345} which is more natural looking for when there is no upper bolunds of interest.

The result is that the same row is returned as with the previous example.

Doing it with PL/SQL

We can of course use the same query parameters with PL/SQL.

set serveroutput on
set define off
declare
  payload clob;

  svc varchar2(200) := 'https://.../ords/rest_demo_schema/vw_rest_svc/';
begin
    dbms_output.put_line('Base:');
    payload := apex_web_service.make_rest_request
       (
         p_url         => svc
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);

    dbms_output.put_line('Limit:');
    payload := apex_web_service.make_rest_request
       (
         p_url         => svc || '?limit=1'
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);

    dbms_output.put_line('Limit and Offset:');
    payload := apex_web_service.make_rest_request
       (
         p_url         => svc || '?offset=3&limit=1'
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);

    dbms_output.put_line('Sorting:');
    payload := apex_web_service.make_rest_request
       (
         p_url         => svc || '?q={"$orderby":{"object_name":"ASC"}}'
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);

    dbms_output.put_line('Sorting + Limit + Offset:');
    payload := apex_web_service.make_rest_request
       (
       p_url         => svc || '?q={"$orderby":{"object_name":"ASC"}}'
                            || '&offset=3&limit=1'
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);

    dbms_output.put_line('Sorting + Limit + Condition on PK:');
    payload := apex_web_service.make_rest_request
       (
       p_url         => svc || '?q={"$orderby":{"object_name":"ASC"},'
                            || '"object_name":{"$between":["AD","Z"]}}&limit=1'
       , p_http_method => 'GET'
       );
    dbms_output.put_line(payload);
    dbms_output.put_line(null);
end;
/

That block of code executes the same exact calls as previously done with cURL, and it of course then has the same exact result.

The paging and offset we’ve used poses some challenges when there are lots of data. Each call will read the same rows and just skip over more and more. For such cases you will want to page by using a filter-condition.

Having looked at how the data can be retrieved using query parameter, we’ll take a look at securing the service in the next post.