Templarios de San Oracle

CREATE OR REPLACE PACKAGE demo_soap AS
 
  /* A type to represent a SOAP RPC request */
  TYPE request IS RECORD (
    method VARCHAR2 (256),
    namespace VARCHAR2 (256),
    BODY VARCHAR2 (32767)
  );
 
  /* A type to represent a SOAP RPC response */
  TYPE response IS RECORD (
    doc XMLTYPE
  );
 
  /* * Create a new SOAP RPC request. */
  FUNCTION new_request (method IN VARCHAR2, namespace IN VARCHAR2)
  RETURN request; 
 
  /* * Add a simple parameter to the SOAP RPC request. */
  PROCEDURE ADD_PARAMETER (
    req   IN OUT NOCOPY request,
    NAME  IN VARCHAR2,
    TYPE  IN VARCHAR2,
    VALUE IN VARCHAR2
  ); /* * Make the SOAP RPC call. */
 
  FUNCTION invoke (
    req    IN OUT NOCOPY request,
    url    IN VARCHAR2,
    action IN VARCHAR2
  )
 
  RETURN response; /*
  * Retrieve the sipmle return value of the SOAP RPC call. */
 
  FUNCTION get_return_value (
    resp      IN OUT NOCOPY response,
    NAME      IN VARCHAR2,
    namespace IN VARCHAR2
  )
  RETURN VARCHAR2;
END;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY demo_soap AS
 
  FUNCTION new_request(
             method IN VARCHAR2, 
             namespace IN VARCHAR2)
  RETURN request AS 
    req request; 
  BEGIN 
    req.method := method; 
    req.namespace := namespace;
 
    RETURN req;
  END;
 
  PROCEDURE add_parameter(
              req IN OUT NOCOPY request,
              name IN VARCHAR2, 
              TYPE IN VARCHAR2, 
              VALUE IN VARCHAR2)
  AS 
  BEGIN 
    req.BODY := req.BODY ||
    '<'||name||' xsi:type="'||type||'">'||value||'</'||name||'>';
  END;
 
  PROCEDURE generate_envelope(
              req IN OUT NOCOPY request, 
              env IN OUT NOCOPY VARCHAR2)
  AS 
  BEGIN 
    env := '<SOAP-ENV:Envelope 
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 
    xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/1999/XMLSchema">
 
    <SOAP-ENV:Body><'||req.method||' '||req.namespace||' 
    SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'||
    req.body||'</'||req.method||'></SOAP-ENV:Body></SOAP-ENV:Envelope>'; 
  END;
 
  PROCEDURE show_envelope(env IN VARCHAR2)  AS 
    i PLS_INTEGER; 
    len PLS_INTEGER; 
  BEGIN 
    i := 1; len:= LENGTH(env); 
    WHILE (i <= len) LOOP 
      DBMS_OUTPUT.put_line(SUBSTR(env, i, 60));
      i := i + 60;
    END LOOP;
  END;
 
  PROCEDURE check_fault(resp IN OUT NOCOPY response) AS 
    fault_node xmltype; 
    fault_code VARCHAR2(256); 
    fault_string VARCHAR2(32767); 
  BEGIN 
    fault_node := resp.doc.EXTRACT('/soap:Fault', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/'); 
    IF (fault_node IS NOT NULL) THEN 
      fault_code := fault_node.EXTRACT('/soap:Fault/faultcode/child::text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
      fault_string := fault_node.EXTRACT('/soap:Fault/faultstring/child::text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
      raise_application_error(-20000, fault_code || ' - ' || fault_string);
    END IF;
  END;
 
  FUNCTION invoke(req IN OUT NOCOPY request, 
    url IN VARCHAR2, 
    action IN VARCHAR2) 
  RETURN response AS 
    env VARCHAR2(32767); 
    http_req UTL_HTTP.req; 
    http_resp UTL_HTTP.resp;
    resp response; 
  BEGIN 
    generate_envelope(req, env); -- show_envelope(env); 
    http_req := UTL_HTTP.begin_request(url, 'POST','HTTP/1.0');
    UTL_HTTP.SET_AUTHENTICATION (HTTP_REQ, 'G016D01/S0162114','Xenios02', 'Basic',TRUE); 
    UTL_HTTP.set_header(http_req, 'Content-Type', 'text/xml');
    UTL_HTTP.set_header(http_req,'Content-Length', LENGTH(env)); 
    UTL_HTTP.set_header(http_req, 'SOAPAction', action); 
    UTL_HTTP.write_text(http_req,env); 
    http_resp := UTL_HTTP.get_response(http_req); 
    UTL_HTTP.read_text(http_resp, env); 
    UTL_HTTP.end_response(http_resp);
    resp.doc := xmltype.createxml(env); 
    resp.doc := resp.doc.EXTRACT('/soap:Envelope/soap:Body/child::node()',
    'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'); -- show_envelope(resp.doc.getstringval());
    check_fault(resp); 
    RETURN resp; 
  END; 
 
  FUNCTION get_return_value(
            resp IN OUT NOCOPY response,
            name IN VARCHAR2,
            namespace IN VARCHAR2)
  RETURN VARCHAR2 AS 
  BEGIN 
    RETURN resp.doc.EXTRACT('//'||name||'/child::text()',namespace).getstringval();
  END;
 
END;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE time_service AS
  FUNCTION get_local_time(zipcode IN VARCHAR2) RETURN VARCHAR2;
END;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY time_service AS 
  -- Location of Web service definition
  -- http://www.ripedev.com/webservices/LocalTime.asmx?WSDL
  FUNCTION get_local_time(zipcode IN VARCHAR2) RETURN VARCHAR2 IS
    req demo_soap.request;
    resp demo_soap.response;
  BEGIN
    req := demo_soap.new_request('LocalTimeByZipCode',
    'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
    demo_soap.add_parameter(req, 'ZipCode', 'xsd:string', zipcode);
    resp := demo_soap.invoke(req,
    'http://www.ripedev.com/webservices/LocalTime.asmx',
    'http://ripedev.com/xsd/ZipCodeResults.xsd/LocalTimeByZipCode');
    RETURN demo_soap.get_return_value(resp, 'LocalTimeByZipCodeResult',
    'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
  END;
 
BEGIN
  /*
  * Since the Web service resides outside of the firewall, we need to set
  * the proxy in the current session before invoking the service.
  */
  UTL_HTTP.set_proxy('proxy.xxx.com', NULL);
  UTL_HTTP.set_persistent_conn_support(TRUE);
END;
/
SHOW ERRORS

SET SERVEROUTPUT ON
SET TIMING ON
BEGIN
  -- He buscado un zip code en http://zip4.usps.com
  DBMS_OUTPUT.PUT_LINE('Hora local de KANSAS CITY' || time_service.get_local_time('64101') );
END;
/
 
Hora local 11/27/2007 6:48:20 AM
 
Procedimiento PL/SQL terminado correctamente.
 
Transcurrido: 00:00:00.44