Sunday, April 27, 2014

Consume Web Service Using PL/SQL

Script to create package :
create or replace
PACKAGE WS_SOAP_CLIENT
AS

FUNCTION SOAP_REQUEST_META(
     l_host_name   VARCHAR2,
    l_string_request VARCHAR2)
  RETURN VARCHAR2;
  
END WS_SOAP_CLIENT;
 

Script to create package body :
create or replace
PACKAGE BODY WS_SOAP_CLIENT AS

  FUNCTION SOAP_REQUEST_META(l_host_name      VARCHAR2,
                             l_string_request VARCHAR2) RETURN VARCHAR2 IS
    l_http_request  UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_buffer_size   NUMBER(10) := 1024;
    l_substring_msg VARCHAR2(2048);
    l_raw_data      RAW(2048);
    l_clob_response CLOB;
    total_xml       VARCHAR2(16384);
  
  BEGIN
  
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url    => 'http://' || l_host_name || '/example-service/endpoints/example.wsdl',
                                             method       => 'POST',
                                             http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request,
                        'User-Agent',
                        'Apache-HttpClient/4.1.1');
    UTL_HTTP.set_header(l_http_request, 'Connection', 'Keep-Alive');
    UTL_HTTP.set_header(l_http_request,
                        'Content-Type',
                        'text/xml;charset=UTF-8');
    UTL_HTTP.set_header(l_http_request,
                        'Content-Length',
                        LENGTH(l_string_request));
  
    <>
    FOR i IN 0 .. CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
      l_substring_msg := SUBSTR(l_string_request,
                                i * l_buffer_size + 1,
                                l_buffer_size);
    
      BEGIN
        l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
        UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT request_loop;
      END;
    END LOOP request_loop;
  
    l_http_response := UTL_HTTP.get_response(l_http_request);
  
    BEGIN
    
      <>
      LOOP
        UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
        l_clob_response := l_clob_response ||
                           UTL_RAW.cast_to_varchar2(l_raw_data);
      END LOOP response_loop;
    
    EXCEPTION
      WHEN UTL_HTTP.end_of_body THEN
        UTL_HTTP.end_response(l_http_response);
    END;
  
    IF (l_http_response.status_code = 200) OR (l_http_response.status_code = 500) then
      total_xml := l_clob_response;
    
    ELSE
      RETURN 'N';
    
    end if;
  
    IF l_http_request.private_hndl IS NOT NULL THEN
      UTL_HTTP.end_request(l_http_request);
    END IF;
    RETURN total_xml;
    
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001, SQLERRM);
    
  END SOAP_REQUEST_META;

END WS_SOAP_CLIENT;

No comments:

Post a Comment