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);

begin
 
  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); 
  utl_http.end_response(t_http_resp);
 
end;

create_procedure

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,

execute_procedure

Reference: UTL_HTTP in Oracle

Karan Balkar About Karan Balkar
self proclaimed extraterrestrial fandroid, computer engineer, amateur gamer and die hard punk rock fan!

3 thoughts on “Tutorial #98: Calling web services using PL/SQL in Oracle

    • Hi Shilpa,
      we have to write a java class to that , and compile that source into SYS or User_schema and grant permisions to execute.

      • Hi! Srinu:

        Is there a way to load the output of a JSON URI into an Oracle database table.

        Below is the sample JSON. But I need to call the URI and parse the JSON and load into a temp table in Oracle database.

        {“MDP_IRQ_DOC”: {
        “MDP_IRQ_MSG”: [
        {“EmployeeID”: “25205274”,”OracleID”: “”,”EmployeeNumber”: “”,”FirstName”: “Mark”,”LastName”: “Smith”,”MiddleName”: “”,”EmpKnownAs”: “”,”Position”: “Principal Chief”,”RehireDt”: “2013-10-14″,”TerminationDt”: “”,”EmpStatus”: “Active”,”ADNetworkID”: “XFDKEMS”,”HomePhone”: “”,”WorkPhone”: “999-999-9999″,”CellPhone”: “888-888-8888″,”PagerNumber”: “”,”EmailID”: “smithM@microdrip.com”,”OrgLevel1Descr”: “IT”,”SystemPersonType”: “EMP”,”ManagerID”: “98437849”,”HRRepEmplID”: “7783749587”,”LocationID”: “100000189”,”Location”: “Detroit”,”OrganizationOrCostCenter”: “IT-Infra”,”HRRepName”: “SwiftNancy”,”LastLoginDt”: “”,”DomainName”: “LUCKY”,”EmployeeClassification”: “”,”JobID”: “I0006″,”ManagerFlag”: “N”,”WorkOrderAFE”: “”,”CWKVendor”: “”,”Company”: “NTP”,”CostCenterNumber”: “8898476”,”EmplStatus”: “A”}
        ]}
        }

Leave a Reply

Your email address will not be published. Required fields are marked *