Tutorial #98: Calling web services using PL/SQL in Oracle

Web services are widely used to as a means of communication between two multiple devices. Oracle also allows developers to consume web services using certain built-in packages. Moreover, one can extend a relational database’s storage, indexing, and searching capabilities to include web services. Through this tutorial, we will learn how to call a simple web service using PL/SQL in Oracle. We would using a public testing platform for services utilizing JavaScript Object Notation (JSON).

Pre-requisites: Oracle 11gR2 database with *SQLPlus, Windows 7 (64 bit)

Step 1: Writing the PL/SQL query

Open the SQL Plus command prompt and login using your database credentials. Once logged in successfully execute the following command in order to enable DBMS_OUTPUT statements.

set serveroutput on

In order to call web services, Oracle provides the UTL_HTTP package. It helps make hyper-text transfer protocol (HTTP) callouts from PL/SQL and SQL. One can use it to access data on the Internet or to call Oracle Web Server cartridges. We would be creating a stored procedure to use the same. Our PL/SQL procedure would be as follows,

create or replace procedure call_webservice as
  t_http_req  utl_http.req;
  t_http_resp  utl_http.resp; 
  t_response_text VARCHAR2 (2000);

  t_http_req:= utl_http.begin_request('http://date.jsontest.com/','GET','HTTP/1.1');
  t_http_resp:= utl_http.get_response(t_http_req);
  UTL_HTTP.read_text(t_http_resp, t_response_text);
  DBMS_OUTPUT.put_line('Response> status_code: "' || t_http_resp.status_code || '"');
  DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||t_http_resp.reason_phrase || '"');    
  DBMS_OUTPUT.put_line('Response> data:' ||t_response_text); 


Step 2: Executing the PL/SQL query

Now, execute the above PL/SQL procedure using the below command,

exec call_webservice

Once the procedure has executed successfully you will see the following output,


Reference: UTL_HTTP in Oracle