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à !
Hi,
Can you please explain the line –
utl_http.set_header(req, ‘Authorization’, ‘Basic f24iYsBpdGVzdDp3ZWJhcGl0ZXN0’);
Is f24iYsBpdGVzdDp3ZWJhcGl0ZXN0 just some random value ?
How is this derived for a given username and password dynamically ?
I am still struggling to call my WebServices from pl/sql. Hopefully your answers will help.
Regards,
Debo
Hi Debo,
The webservice I need to call requires Base64 encoded username and password. This is a simple encoding mechanism that encodes the string “username:password” (replacing each field). You can use the following sites to perform the encoding:
https://www.blitter.se/utils/basic-authentication-header-generator/
https://www.base64encode.org/
Hope that helps,
Miguel Anjo
i tired the ways which was mentioned in the above article but will excute the program i am getting the below errors
ORA-29273: HTTP request failed
ORA-53203: security violation
ORA-06512: at “SYS.UTL_HTTP”, line 368
ORA-06512: at “SYS.UTL_HTTP”, line 1118
ORA-06512: at “ICBS.PUBLISH_EVENT”, line 13
ORA-06512: at line 1
You need to check on alertlog/trace files the reason for the ORA-53203.