Consume Digest API with PLSQL

In my personal blog, I have created an digest authenticated API use nodejs. Only thing it does is to authenticate with digest and output a json string when you are authenticated. I made it so that I can consume it in PLSQL.

Here is the code snippet I used in the index.js (github)

var express = require('express');
var http = require('http');
const auth = require('http-auth');
var digest = auth.digest({
    realm: 'Sample',
    file: __dirname + "/users.htdigest"
});
var app = express();
http.createServer(app,digest.check());
app.get('/digestapi',digest.check((req,res)=>{
    console.log(req.auth);
    res.send({ username: 'whatever json' });
}));
app.listen(3000,()=>{
    console.log('running on port 3000');
});

It is running now on my newly created server API.

Consume the API in PLSQL

Note that I am doing this in my Autonomous database in the cloud. If you do this in your local 18 c XE, code might not be the same, especially when it comes to the usage of utl_http package.

My goal was to test both with utl_http package and apex_web_service package.

• code snippet for apex_web_service

create or replace procedure trydigestapi_apex_web_s is
  l_clob        clob;
  l_result      clob;
  l_url         varchar2(4000) := 'https://www.chaoyu.nl/digestapi/';
  l_http_method varchar2(10) := 'GET';
  l_return clob;
begin
  apex_web_service.g_request_headers.delete;
  l_return := apex_web_service.make_rest_request(p_url         => l_url
                                                ,p_http_method => l_http_method
                                                ,p_username    => 'chaoyu'
                                                ,p_password    => '2389'
                                                ,p_scheme      => 'Digest');
  dbms_output.put_line(l_return);
end trydigestapi_apex_web_s;

result

• code snippet for utl_http

create or replace procedure trydigestapi_utl_http is
  req               utl_http.req;
  res               utl_http.resp;
  url               varchar2(4000) := 'https://www.chaoyu.nl/digestapi/';
  buffer            clob;
  l_header_name     varchar2(255);
  l_header_value    varchar2(255);
  l_www_auth_header varchar2(1000); 
begin
  -- see details how to use utl_http in oracle cloud autonomous database
  -- Create an Access Control List for the host 
  -- *** dbms_network_acl_admin has to be grant execute by Admin to DEMO 
  -- see details https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/appendix-database-pl-sql-packages-restrictions.html#GUID-829A7D07-1EA4-4F59-AA60-F780FABAFDEC
  dbms_network_acl_admin.append_host_ace(host => 'www.chaoyu.nl'
                                        ,ace  => xs$ace_type(privilege_list => xs$name_list('http')
                                                            ,principal_name => 'DEMO'
                                                            ,principal_type => xs_acl.ptype_db));
  utl_http.set_wallet('');
  req := utl_http.begin_request(url, 'GET');
  begin
    res := utl_http.get_response(req);
  
    for i in 1 .. utl_http.get_header_count(res)
    loop
      utl_http.get_header(res, i, l_header_name, l_header_value);
      --dbms_output.put_line(l_header_name || ': ' || l_header_value);
      if l_header_name = 'WWW-Authenticate'
      then
        l_www_auth_header := l_header_value;
      end if;
    end loop;
  
    if l_www_auth_header is null
    then
      raise_application_error(-20001, 'Unknown Auth Methods');
    end if;
  
    req := utl_http.begin_request(url, 'GET');
    utl_http.set_authentication(req, 'chaoyu', '2389', 'Digest');
    utl_http.set_header(req, l_www_auth_header);
    begin
      res := utl_http.get_response(req);
    
      loop
        utl_http.read_line(res, buffer);
        dbms_output.put_line(buffer);
      end loop;
    
    exception
      when utl_http.end_of_body then
        utl_http.end_response(res);
    end;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(res);
  end;
exception
  when utl_http.too_many_requests then
    utl_http.end_response(res);
end trydigestapi_utl_http;

Result

The code for utl_http is much more complicated than apex_web_service. But if you dont have APEX installed on your database, utl_http can come in handy. Here is some information about using utl_http in oracle autonomous data in the cloud.

For people who want to try this api on oracle 18 XE in your local machine, you need to download the SSL certification from www.chaoyu.nl. Details are explained here. Or a better one time solution here.