twilio integration for Oracle Autonomous Database (ATP)

I had difficult time on finding a way to send WhatsApp Messages via Oracle ATP and dumping my findings here for the sake of saving time of others.

1. Create Network ACL

We have to create an Access Control List called twilio_acl.xml that is associated with a database user (example_user) . For this you have to execute following pl/sql as ADMIN user on ATP (or sys user on normal oracle installations)

grant execute on utl_http to example_user
grant execute on dbms_lock to example_user

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ‘twilio_acl.xml’,
description => ‘twilio ACL ’,
principal => ‘example_user’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
end;

begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => ‘twilio_acl.xml’,
host => ‘api.twilio.com’,
lower_port => 443,
upper_port => NULL);
end;

2. Create Stored Procedure for message sending

Now you have to execute following pl/sql under your specific db user (ie. example_user).

Special Note: I have used APEX_UTIL to http encode parameters. Kindly use some alternative way of doing http encoding if you do not have APEX_UTIL function (I am using APEX on ATP for my work).

create or replace
procedure send_twilio_msg
( msgbody in varchar2,
msgto in varchar2,
msgfrom in varchar2,
accid in varchar2,
acctoken in varchar2
) is
req utl_http.req;
res utl_http.resp;
buffer varchar2(4000);
v_param VARCHAR2(500) := ‘To=’||APEX_UTIL.URL_ENCODE(msgto)||’&From=’||APEX_UTIL.URL_ENCODE(msgfrom)||’&Body=’||APEX_UTIL.URL_ENCODE(msgbody);
v_param_length NUMBER := length(v_param);
url varchar2(4000) := ‘https://api.twilio.com/2010-04-01/Accounts/'||accid||'/Messages.json?';

begin

UTL_HTTP.SET_WALLET(‘’);
req := utl_http.begin_request(url, ‘POST’,’ HTTP/1.1');
utl_http.set_header(req, ‘user-agent’, ‘mozilla/4.0’);
UTL_HTTP.SET_AUTHENTICATION(req, accid, acctoken);
utl_http.set_header(req, ‘content-type’, ‘application/x-www-form-urlencoded’);
UTL_HTTP.SET_HEADER (r => req,
name => ‘Content-Length’,
value => v_param_length);
UTL_HTTP.WRITE_TEXT (r => req,
data => v_param);
res := utl_http.get_response(req);

— process the response from the HTTP call
begin
loop
utl_http.read_line(res, buffer);
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 send_twilio_msg;

3. Test twilio messaging

Finally you can start using newly created stored procedure to send messages via twilio.

SP Usage:

send_twilio_msg(‘message’,’channel:toNumber’,’channel:fromNumber,’twilio_AccountSid’,’twilio_auth_token’);

Example:

begin
send_twilio_msg(‘hello world’,’whatsapp:+947570000001',’whatsapp:+14155000000',’AC1c8f7d99a123456788',’3d773780f2ssssb6c6df2c1d4e’);
end;

Enjoy