This week, let's look at making web requests from the Oracle database.
With the rise of REST Web services, it's increasing important that we DBA folk know how to initiate web requests from the database. Numerous places have information on this, but what they all seem to lack is some really good examples on the matter. With that, let's look at some code samples that will help remedy this.
Before we get started, it's important to note that all web/API calls from the database are done so using the UTL_HTTP package. You can find more information on this (ad nausem) here.
1. Making Standard HTTP GET Request. In this example, we are calling the URL http://www.nyquest.com. The output of this will be dumped into the variable resp. You will then need to LOOP through this to get all the output.
set serveroutput on
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
req := UTL_HTTP.BEGIN_REQUEST('http://www.nyquest.com');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/
2. Making HTTP GET Calls passing in parameters. Passing in HTTP GET parameters is no problem, just pass them via the URL. In the example below we are passing PARM1=hello and PARM2=world. We are going to "set define off" since this code has an '&' character which will cause problems in SQL PLUS.
set define off
set serveroutput on
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
req := UTL_HTTP.BEGIN_REQUEST('http://www.nyquest.com?parm1=hello&parm2=world');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/
3. Making HTTP GET Calls through a proxy server. Making a call via a internal proxy server is no problem, simply include the proxy server URL in the UTL_HTTP.SET_PROXY procedure.
set define off
set serveroutput on
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
-- set proxy server address here
UTL_HTTP.SET_PROXY(http://proxy.nyquest.com:8000);
req := UTL_HTTP.BEGIN_REQUEST('http://www.nyquest.com?parm1=hello&parm2=world');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/
4. Making HTTPS GET Calls. If you need to run a call to an HTTPS site, you must first import the rootCA of the site in question into an Oracle Wallet file, and then drop the file on the database server, then specify the location/credentials of that file in the UTL_HTTP.SET_WALLET call.
set define off
set serveroutput on
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
UTL_HTTP.SET_WALLET ('file:/usr/local/SSL/wallet','u_pick_it');
req := UTL_HTTP.BEGIN_REQUEST('https://www.nyquest.com?parm1=hello&parm2=world');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/
5. Making HTTP POST Calls. If you want to make an HTTP POST call, then you must setup the post string prior to the call. The format of the POST string is parm1=value1&parm2=value2... In this example we are doing a POST call with parm1=hello and parm2=world
set define off
set serveroutput on
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
-- prepare POST string for web service
t_param := 'parm1=hello\&parm2=world';
t_param_length := length(t_param);
UTL_HTTP.SET_WALLET ('file:/usr/local/SSL/wallet','u_pick_it');
req := UTL_HTTP.BEGIN_REQUEST(url=>'https://www.nyquest.com',method=>'post');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.SET_HEADER (r => req, name => 'Content-Type', value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER (r => req, name => 'Content-Length', value => t_param_length);
UTL_HTTP.WRITE_TEXT (r => req, data => t_param);
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END;
/