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

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

 1. add 'Delete' Del column to Report Query 2. Set the Following Properties for the DEL Column Type: Link Heading: Delete Targ...