Wednesday, December 11, 2019

How to consume Web Service (POST) in Oracle


SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
   P_REPORT_CODE   VARCHAR2 (100) := '111.111';
   P_OFFSET        NUMBER := 1;
   P_LIMIT         NUMBER := 20000;
   req             UTL_HTTP.req;
   resp            UTL_HTTP.resp;
   l_buffer        VARCHAR2 (32767);
   x_clob          CLOB;
   my_scheme       VARCHAR2 (256);
   my_realm        VARCHAR2 (256);
   my_proxy        BOOLEAN;
   url             VARCHAR2 (500);
   content         VARCHAR2 (4000);
BEGIN
   url :=
         'https://test-stage.oracle.com/ws/report/execute/'
      || P_REPORT_CODE
      || '?offset='
      || P_OFFSET
      || '&limit='
      || P_LIMIT;

   content :=
         '{
"modified_date_from":  "'
      || TO_CHAR (SYSDATE - 2, 'DD-MON-YYYY')
      || '",
"modified_date_to": "'
      || TO_CHAR (SYSDATE, 'DD-MON-YYYY')
      || '",
"content_object_id": "47630968",
"sDateDisplay": "E"
}';

   UTL_TCP.close_all_connections ();
   UTL_HTTP.set_wallet ('file:/u01/app/oracle/dba/rdbms/test/wallet');
   UTL_HTTP.set_proxy ('http://dmz-proxy-test.us.oracle.com:80');
   UTL_HTTP.set_response_error_check (FALSE);
   req := UTL_HTTP.begin_request (url, 'POST', 'HTTP/1.1');
   UTL_HTTP.set_body_charset (req, 'UTF-8');
   UTL_HTTP.set_header (
      req,
      'Authorization',
      'Bearer abcdefghijklmnopqrstuvwxyz');
   UTL_HTTP.set_header (req, 'Content-Type', 'application/json');
   UTL_HTTP.set_header (req, 'Content-Length', LENGTHB (content));
   UTL_HTTP.set_header (req, 'Connection', 'Keep-Alive');
   DBMS_OUTPUT.put_line ('url=' || url);
   DBMS_OUTPUT.put_line ('content=' || content);
   UTL_HTTP.write_text (req, content);
   resp := UTL_HTTP.get_response (req);
   DBMS_OUTPUT.put_line ('HTTP response status code: ' || resp.status_code);
   DBMS_OUTPUT.put_line (
      'HTTP response reason phrase: ' || resp.reason_phrase);

   /* Copy the response to local*/
   DBMS_LOB.createtemporary (x_clob, FALSE);
   DBMS_LOB.open (x_clob, DBMS_LOB.lob_readwrite);

   BEGIN
      LOOP
         UTL_HTTP.read_text (resp, l_buffer);
         DBMS_LOB.writeappend (x_clob, LENGTH (l_buffer), l_buffer);
      END LOOP;
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         DBMS_OUTPUT.PUT_LINE ('x_clob=' || x_clob);
         UTL_HTTP.END_RESPONSE (resp);
   END;
EXCEPTION
   WHEN UTL_HTTP.end_of_body
   THEN
      UTL_HTTP.end_response (resp);
   WHEN UTL_HTTP.too_many_requests
   THEN
      UTL_HTTP.end_response (resp);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Oracle Error:' || SQLERRM);
END;
/

No comments:

Post a Comment