REST

Make a REST call with JSON content to a HTTPS webservice from PL/SQL 4

This entry was posted in JSON REST on 04.09.2019 by Miguel

All keywords are now in the title. 🙂

I’ve started developing this small PL/SQL procedure based on Lucas Jelema blog entry Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE .

Unfortunately it took me a while to find out why I was getting the error:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>400 Bad Request</title>
</head><body>
<h1>Bad Request</h1>
<p>Your browser sent a request that this server could not understand.<br />
</p>
</body></html>

There is a empty space at the beginning of a variable in the example on Lucas’ blog post – before the HTTP/1.1 on the line below:

req := utl_http.begin_request(url, 'POST',' HTTP/1.1');

My need is a bit more complex, so I leave here the complete code.

Certificate and Oracle wallet

First you need to download the certificate. A good tutorial do to this is at Shellhacks.com.

Then you need to create a wallet on the database server and add the certificate:

mkdir ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
orapki wallet create -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -pwd WalletPassword123 -auto_login
orapki wallet add -wallet ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet -trusted_cert -cert "/tmp/my_certificate.crt" -pwd WalletPassword123

Oracle ACLs

On the database (my case was Oracle 11.2) you need to have XDB installed and add the ACLs. I skip the instructions to install XDB. The user is called GREEN.

grant execute on utl_http to GREEN;
grant execute on dbms_lock to GREEN;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_file.xml', 
    description  => 'Allows sending alerts to REST service',
    principal    => 'GREEN',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;
/

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'acl_file.xml',
    host        => 'my_rest_service.com', 
    lower_port  => 443,
    upper_port  => NULL);    
end; 
/

Procedure to invoke REST service

The rest service requires a username password. I use the encoded version, so there are no visible passwords in the code. You can encode the pair username:password here.

CREATE OR REPLACE PROCEDURE green.publish_event
( p_robot in varchar2, p_business_service in varchar2, p_target_ci in varchar2, p_transaction in varchar2, p_status in number, p_message in varchar2) is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'https://my_rest_service.com/api/event';
  buffer varchar2(32767); 
  content varchar2(8000) := '{ "robot":"'||p_robot||'", "business_service":"'||p_business_service||'", "target_ci":"'||p_target_ci||'", "transaction":"'||p_transaction||'", "status":"'||p_status||'", "message":"'||p_message||'", "target_support_group": "", "organization": "ANJO_EVENT"}';
  
BEGIN
  utl_http.set_wallet('file:${ORACLE_BASE}/admin/${ORACLE_SID}/wallet', null);
	req  :=UTL_HTTP.begin_request(url, 'POST');
	utl_http.set_header(req, 'Authorization', 'Basic f24iYsBpdGVzdDp3ZWJhcGl0ZXN0'); 
  utl_http.set_header(req, 'Content-Length', length(content));
  utl_http.write_text(req, content);

  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_text(res, buffer,32767);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
END publish_event;
/

Trigger to automatically send event

In my case, the idea is to send an event every time a line is inserted into a table. So I just create a small trigger that calls the procedure above.

CREATE OR REPLACE TRIGGER green.trg_table1_monitor
AFTER INSERT
   ON green.table1_monitor
   FOR EACH ROW
BEGIN
   publish_event (
	   p_robot => :new.host_name,
		 p_business_service => 'IT DB',
		 p_target_ci => '356224bs5b1f6f401d2262eb0b9619ad',
		 p_transaction => 'Table1',
		 p_status => 7,
		 p_message => 'Green-Table1 -Exception: '|| substr(:new.exception,0,100) 
	  );
END;
/

Et voilà!

Blog: Keyword Oracle