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

Now that we have seen various ways to get data and validate the REST-service working we can move on to implement access from within an Oracle database. I find validating outside the database as the first step to be very valuable. If you have one database that has the rest enabled view, the rest service and then a view in another database reading from that view setup in one go and then it does not work right – there are so many moving parts and som much abstraction that it makes troubleshooting very hard.

Once you can get it to work in cURL, it is usually straightforward to access the same endpoint from within a database.

First we need a user that can make the call to the rest service.

create user rest_demo_caller identified by REST_1234_caller
  default tablespace data
  temporary tablespace temp;

grant create session   to rest_demo_caller;
grant create view      to rest_demo_caller;
grant create procedure to rest_demo_caller;

Now we can log in as rest_demo_caller and test the rest service. Note that the user having the rest enabled view has a separate name from this user that will invoke it. Typically you’d access a rest-service from one database that accesses another. But it is setup here to allow testing it using just one database.

set serveroutput on
declare
  payload clob;
begin
    payload := apex_web_service.make_rest_request
        (
          p_url => 'https://.../ords/rest_demo_schema/vw_rest_svc/'
        , p_http_method => 'GET'
        );
    dbms_output.put_line(payload);
end;
/

The result will be identical to that we got from the cURL-call. Just returned in your SQL-tool of choice. I’m using apex_web_service as it simplifies things, we’ll not build an APEX-app here were just using some of its neat infrastructure packages.

If you get an error referring to ACL or access control then you have not granted the database access to make a call to the ORDS-server. This will not happen in an autonomous database, but if you run roll-your-own it may happen. Then modify the following according to your environment and run in the database you want to make the REST-call from.

declare
  l_principal varchar2(20) := 'APEX_210200';
begin
  dbms_network_acl_admin.create_acl (
    acl          => 'ords_acl.xml', 
    description  => 'My ORDS-server',
    principal    => l_principal,
    is_grant     => true, 
    privilege    => 'connect',
    start_date   => systimestamp,
    end_date     => null);

  dbms_network_acl_admin.assign_acl (
    acl         => 'ords_acl.xml',
    host        => '<The name of you ordsserver goes here', 
    lower_port  => 80,
    upper_port  => 80); 

  commit;
end;
/

Modify it so it references your APEX-version. If you have not installed it you’ll have to look at alternative ways to make the rest-call. Then the name of your ORDS-server is what you enter in the host-parameter. Just the host, no prefixing hit http or adding patch to the end. Only the name of the server.

Back to accessing the service from the database. So we have seen that we can get the JSON-response with PL/SQL. Let’s now do the same with SQL.

select apex_web_service.make_rest_request
            (
              p_url => 'https://.../ords/rest_demo_schema/vw_rest_svc/'
            , p_http_method => 'GET'
            )
  from dual;
/

Again we get the identical output only now returned as a query result.

We could of course wrap this into a view and have it returned. But it is still just a raw JSON and we would of course want to get the data split up into the rows it is fetching from the rest-enabled view. The next post will be spent on looking a bit at how to get from raw JSON to get the data represented as separate columns.