Oracle SQL & PL/SQL : Consuming SOAP Web Services using Oracle UTL

SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
/* $Header: $ */
-- ------------------------------------------------------------------------------------------------
-- $URL: $
-- $LastChangedRevision: $
-- $LastChangedDate: $
-- $LastChangedBy: $
-- $Id: $
-- ------------------------------------------------------------------------------------------------
-- Date Name Comment
-- ----------- -------------- ---------------------------------------------------------------------
-- 03-Aug-2018 Rajasekhar M - To test SOAP WebService connection
---------------------------------------------------------------------------------------------------

i_endpoint_url VARCHAR2 (100) := 'https://wsctt.pearsonvue.com/cxfws2/services/Ping?wsdl';

--i_endpoint_url VARCHAR2 (100):= 'https://wsctt.pearsonvue.com/cxfws2/services/Ping';

i_req_xml XMLTYPE
:= xmltype (
'<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:sch="http://ws.pearsonvue.com/ping/schema">
<soap:Header/>
<soap:Body>
<sch:pingServiceRequest/>
</soap:Body>
</soap:Envelope>');

o_status_code NUMBER := NULL;
o_status_desc VARCHAR2 (500) := NULL;
o_resp_xml CLOB := NULL;

PROCEDURE p_print (pnt_msg VARCHAR2)
AS
BEGIN
IF 1 = 1
THEN
DBMS_OUTPUT.put_line (SYSTIMESTAMP || ': ' || pnt_msg);
RETURN;
END IF;
END p_print;

FUNCTION cloblengthb (p_clob IN CLOB)
RETURN NUMBER
AS
v_temp_blob BLOB;
v_dest_offset NUMBER := 1;
v_src_offset NUMBER := 1;
v_amount INTEGER := DBMS_LOB.lobmaxsize;
v_blob_csid NUMBER := DBMS_LOB.default_csid;
v_lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
v_warning INTEGER;
v_total_size NUMBER := 0; -- Return total clob length in bytes
BEGIN
IF p_clob IS NOT NULL
THEN
DBMS_LOB.CREATETEMPORARY (lob_loc => v_temp_blob, cache => TRUE);
DBMS_LOB.CONVERTTOBLOB (v_temp_blob,
p_clob,
v_amount,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_ctx,
v_warning);
v_total_size := DBMS_LOB.GETLENGTH (v_temp_blob);
DBMS_LOB.FREETEMPORARY (v_temp_blob);
ELSE
v_total_size := NULL;
END IF;

RETURN v_total_size;
END cloblengthb;

PROCEDURE p_invoke_wsdl_over_https (i_endpoint_url IN VARCHAR2,
i_req_xml IN CLOB,
o_status_code OUT NUMBER,
o_status_desc OUT VARCHAR2,
o_resp_xml OUT CLOB)
AS
v_wsdl_url VARCHAR2 (1000) := NULL;
v_soap_request CLOB;
v_soap_response CLOB;
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
v_response_buffer VARCHAR2 (32767) := NULL;
v_status_code NUMBER := 0; -- Default value is 0 - Success;
v_status_desc VARCHAR2 (500) := 'Success'; -- Default value is Success';
v_process_start_time NUMBER;
v_process_end_time NUMBER;
v_process_time_taken NUMBER;
v_host VARCHAR2 (100) := NULL;
g_env VARCHAR2 (100)
:= UPPER (SYS_CONTEXT ('USERENV', 'DB_NAME', 15));
BEGIN
v_process_start_time := DBMS_UTILITY.GET_TIME;
p_print ('Starting invoking webservice transaction process.');
v_wsdl_url := i_endpoint_url;
v_soap_request := i_req_xml;
p_print ('v_wsdl_url:' || v_wsdl_url);

/*Setting set_response_error_check to true will raise an exception when the status code indicates an error.a status code in the 4xx or 5xx ranges Response error check is turned off by default*/
UTL_HTTP.set_response_error_check (enable => TRUE);
-- Sets the UTL_HTTP package to raise a detailed exception
UTL_HTTP.set_detailed_excp_support (enable => TRUE);
-- sets the proxy to be used for requests of the HTTP or other protocols
UTL_HTTP.set_proxy (proxy => 'http://www-proxy-adcq7.us.oracle.com:80');
-- sets the Oracle wallet used for all HTTP requests over Secured Socket Layer (SSL), namely HTTPS
UTL_HTTP.SET_WALLET ('file:/u01/app/oracle/dba/rdbms/ouappprd/wsctt_102019', 'ou96ffd3482652ab94f2a45c6b61ef1cgbs');
-- From Oracle 11gR2 onward, if you are using the -auto_login option on the wallet, you don't have to specify the wallet password. You just pass NULL instead of the password.
p_print ('Wallet has been set correctly......');
-- sets the default time out value (60) for all future HTTP requests
UTL_HTTP.set_transfer_timeout (60);

-- HTTP_VERSION_1_0 Denotes HTTP version 1.0 (HTTP/1.0)
-- HTTP_VERSION_1 Denotes HTTP version 1.1 (HTTP/1.1)
-- Begins a new HTTP request.UTL_HTTP establishes the network connection to the target Web server or the proxy server and sends the HTTP request line.

l_http_request :=
UTL_HTTP.begin_request (url => v_wsdl_url,
method => 'POST', --GET/POST
http_version => 'HTTP/1.2');

p_print ('Connection has opened successfully......');
UTL_HTTP.set_body_charset (l_http_request, 'UTF-8');
-- Sets an HTTP request header. The request header is sent to the Web server as soon as it is set.
UTL_HTTP.set_header (l_http_request,
'User-Agent',
'Mozilla/4.0 (compatible)');
UTL_HTTP.set_header (l_http_request, 'Connection', 'Keep-Alive'); -- Keep-Alive/close
-- SOAP 1.1
/*UTL_HTTP.set_header (l_http_request,
'Content-Type',
'text/xml; charset=utf-8');*/
-- SOAP 1.2
UTL_HTTP.set_header (l_http_request,
'Content-Type',
'application/soap+xml; charset=utf-8');

UTL_HTTP.set_header (l_http_request,
'content-length',
cloblengthb (v_soap_request));
-- Sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request.
--UTL_HTTP.set_authentication (req, v_organisation_token, '.');
-- Writing soap request text data in the HTTP body.
UTL_HTTP.write_text (l_http_request, v_soap_request);
p_print ('v_soap_request: ' || v_soap_request);

l_http_response := UTL_HTTP.get_response (l_http_request);
p_print (
'Response> status_code: "' || l_http_response.status_code || '"');
p_print (
'Response> reason_phrase: "' || l_http_response.reason_phrase || '"');
p_print (
'Response> http_version: "' || l_http_response.http_version || '"');

DBMS_LOB.createtemporary (v_soap_response, TRUE);

BEGIN
LOOP
p_print ('Read data.................');
-- reads the HTTP response body in text form and returns the output in the caller-supplied buffer
UTL_HTTP.read_text (l_http_response, v_response_buffer, 32767);
p_print ('v_response_buffer ' || v_response_buffer);
DBMS_LOB.writeappend (v_soap_response,
-- LENGTHB (v_response_buffer),
LENGTH (v_response_buffer),
v_response_buffer);
END LOOP;
EXCEPTION
-- The end_of_body exception will be raised if the end of the HTTP response body is reached
WHEN UTL_HTTP.end_of_body
THEN
p_print ('end_of_body error -->' || SQLCODE || ':' || SQLERRM);
-- ends the HTTP response. It completes the HTTP request and response
UTL_HTTP.end_response (l_http_response);
WHEN OTHERS
THEN
p_print (
'Response process error -->' || SQLCODE || ':' || SQLERRM);
END;

p_print ('v_soap_response ' || v_soap_response);
o_resp_xml := v_soap_response;
o_status_code := v_status_code;
o_status_desc := v_status_desc;
v_process_end_time := DBMS_UTILITY.GET_TIME;
v_process_time_taken :=
MOD (
( (v_process_end_time - v_process_start_time) + POWER (2, 32)),
POWER (2, 32))
/ 100;
p_print (
'invoking webservice transaction process took '
|| v_process_time_taken
|| ' Seconds');
p_print ('Ending invoking webservice transaction process.');
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
UTL_HTTP.end_response (l_http_response);
WHEN UTL_HTTP.too_many_requests
THEN
UTL_HTTP.end_response (l_http_response);
v_status_code := 1;
v_status_desc := ('Failed, ' || 'Oracle exception - ' || SQLERRM);
o_status_code := v_status_code;
o_status_desc := v_status_desc;
WHEN OTHERS
THEN
v_status_code := 1;
v_status_desc := ('Failed, ' || 'Oracle exception - ' || SQLERRM);
o_status_code := v_status_code;
o_status_desc := v_status_desc;
p_print ('Oracle Error code -->' || SQLCODE);
p_print ('Oracle Error Message -->' || SQLERRM);
END p_invoke_wsdl_over_https;
BEGIN
p_invoke_wsdl_over_https (i_endpoint_url,
i_req_xml.getclobval (),
o_status_code,
o_status_desc,
o_resp_xml);
DBMS_OUTPUT.put_line ('o_status_code=' || o_status_code);
DBMS_OUTPUT.put_line ('o_status_desc=' || o_status_desc);
DBMS_OUTPUT.put_line ('o_resp_xml=' || o_resp_xml);
END;
/

Response:

19-NOV-19 03.29.34.666236000 AM -07:00: Starting invoking webservice transaction process.

19-NOV-19 03.29.34.666360000 AM -07:00:

v_wsdl_url:https://wsctt.pearsonvue.com/cxfws2/services/Ping?wsdl

19-NOV-19 03.29.34.666467000 AM -07:00: Wallet has been set correctly......

19-NOV-19 03.29.34.973263000 AM -07:00: Connection has opened successfully......

19-NOV-19 03.29.34.974082000 AM -07:00: v_soap_request: <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:sch="http://ws.pearsonvue.com/ping/schema">

<soap:Header/>

<soap:Body>

<sch:pingServiceRequest/>

</soap:Body>

</soap:Envelope>

19-NOV-19 03.29.35.281206000 AM -07:00: Response> status_code: "200"

19-NOV-19 03.29.35.281250000 AM -07:00: Response> reason_phrase: "200"

19-NOV-19 03.29.35.281264000 AM -07:00: Response> http_version: "HTTP/1.1"

19-NOV-19 03.29.35.281304000 AM -07:00: Read data.................

19-NOV-19 03.29.35.281359000 AM -07:00: v_response_buffer <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

<result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

19-NOV-19 03.29.35.281543000 AM -07:00: Read data.................

19-NOV-19 03.29.35.281666000 AM -07:00: end_of_body error -->-29266:ORA-29266: end-of-body reached

19-NOV-19 03.29.35.282175000 AM -07:00: v_soap_response <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

<result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

19-NOV-19 03.29.35.282235000 AM -07:00: invoking webservice transaction process took .62 Seconds

19-NOV-19 03.29.35.282250000 AM -07:00: Ending invoking webservice transaction process.

o_status_code=0

o_status_desc=Success

o_resp_xml=<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

<result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

PL/SQL procedure successfully completed.

SQL>

Oracle SQL & PL/SQL

Tuesday, November 19, 2019

Consuming SOAP Web Services using Oracle UTL_HTTP

No comments:

Post a Comment

Delete Row Button to all records of Interactive Report in Oracle Apex

Report Abuse