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;
Step 2: Executing the PL/SQL query
Now, execute the above PL/SQL procedure using the below command,
Once the procedure has executed successfully you will see the following output,
Reference: UTL_HTTP in Oracle
self proclaimed extraterrestrial fandroid, computer engineer, amateur gamer and die hard punk rock fan!