Serviços Web no Oracle

--   SYS
DECLARE
    l_servlet_name      VARCHAR2(32)  := 'orawsv';
BEGIN
    --   orawsv   Native Web Services
    DBMS_XDB.deleteServletMapping(l_servlet_name);
    DBMS_XDB.deleteServlet(l_servlet_name);
 
    DBMS_XDB.addServlet( NAME     => l_servlet_name
                       , LANGUAGE => 'C'
                       , DISPNAME => 'Oracle Query Web Service'
                       , DESCRIPT => 'Servlet for issuing queries as a Web Service'
                       , SCHEMA   => 'XDB');
 
    DBMS_XDB.addServletSecRole( SERVNAME => l_servlet_name
                              , ROLENAME => 'XDB_WEBSERVICES'
                              , ROLELINK => 'XDB_WEBSERVICES');
 
    DBMS_XDB.addServletMapping( PATTERN => '/orawsv/*'
                              , NAME    => l_servlet_name);
    COMMIT;
END;
/

--   SYS
DECLARE
    l_ws_user       VARCHAR2(32)  := 'WS_SOAP_TEST'; --    ,        WS
    l_acl           VARCHAR2(250) := 'acl_allow_all.xml';
    l_tmp           VARCHAR2(250);
BEGIN
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES TO "'||l_ws_user||'"';
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES_OVER_HTTP TO "'||l_ws_user||'"';
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES_WITH_PUBLIC TO "'||l_ws_user||'"';
 
    --       PL/SQL 
    BEGIN
        dbms_network_acl_admin.drop_acl(acl => '/sys/acls/'||l_acl);
    EXCEPTION
        WHEN dbms_network_acl_admin.acl_not_found THEN
            NULL;
    END;
 
    --  ACL
    dbms_network_acl_admin.create_acl( acl         => l_acl
                                     , description => 'Allow all connections'
                                     , is_grant    => TRUE
                                     , start_date  => SYSTIMESTAMP
                                     , end_date    => NULL
                                     , principal   => 'SYS'
                                     , privilege   => 'connect');
 
    dbms_network_acl_admin.assign_acl( acl        => l_acl
                                     , host       => '*'
                                     , lower_port => NULL
                                     , upper_port => NULL);
 
    --    (resolve) 
    dbms_network_acl_admin.add_privilege( acl        => l_acl
                                        , principal  => l_ws_user
                                        , is_grant   => TRUE
                                        , privilege  => 'resolve'
                                        , POSITION   => NULL
                                        , start_date => SYSTIMESTAMP
                                        , end_date   => NULL);
 
    COMMIT;
END;
/

<definitions name="PK_NATIVE_WS_TEST"
    targetNamespace="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
    xmlns="http://schemas.xmlsoap.org/wsdl/"
    xmlns:tns="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
  <types>
    <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
     elementFormDefault="qualified">
      <xsd:element name="PROC_SIMPLEInput">
        <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="A_DATA-VARCHAR2-IN" type="xsd:string"/>
              <xsd:element name="A_ID-NUMBER-IN" type="xsd:integer"/>
            </xsd:sequence>
          </xsd:complexType>
      </xsd:element>
 
      <xsd:element name="PROC_SIMPLEOutput">
        <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="O_RESULT" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
      </xsd:element>
   </xsd:schema>
  </types>
 
  <message name="PROC_SIMPLEInputMessage">
    <part name="parameters" element="tns:PROC_SIMPLEInput"/>
  </message>
 
  <message name="PROC_SIMPLEOutputMessage">
    <part name="parameters" element="tns:PROC_SIMPLEOutput"/>
  </message>
 
  <portType name="PK_NATIVE_WS_TESTPortType">
  <operation name="PROC_SIMPLE">
      <input message="tns:PROC_SIMPLEInputMessage"/>
      <output message="tns:PROC_SIMPLEOutputMessage"/>
    </operation>
  </portType>
 
  <binding name="PK_NATIVE_WS_TESTBinding"
           type="tns:PK_NATIVE_WS_TESTPortType">
    <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
    <operation name="PROC_SIMPLE">
      <soap:operation
 soapAction="PROC_SIMPLE"/>
      <input>
        <soap:body parts="parameters" use="literal"/>
      </input>
      <output>
        <soap:body parts="parameters" use="literal"/>
      </output>
    </operation>
  </binding>
 
  <service name="PK_NATIVE_WS_TESTService">
    <documentation>Oracle Web Service</documentation>
    <port name="PK_NATIVE_WS_TESTPort" binding="tns:PK_NATIVE_WS_TESTBinding">
       <soap:address 
             location="http://******:8080/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"/>
     </port>
  </service>
 
</definitions>

BEGIN
    ORDS.DEFINE_MODULE( p_module_name    => 'shop'
                      , p_base_path      => 'shop'
                      , p_items_per_page =>  25
                      , p_status         => 'PUBLISHED'
                      , p_comments       => NULL);
 
    ORDS.DEFINE_TEMPLATE( p_module_name    => 'shop'
                        , p_pattern        => 'order'
                        , p_priority       => 0
                        , p_etag_type      => 'HASH'
                        , p_etag_query     => NULL
                        , p_comments       => NULL);
 
    COMMIT;
END;
/

CREATE TABLE T_WS_LOG
(
  id_log          NUMBER GENERATED ALWAYS AS IDENTITY,
  message         VARCHAR2(2000),
  request_header  VARCHAR2(2000),
  request_body    CLOB,
  response_header VARCHAR2(2000),
  response_body   CLOB,
  dtz_log         TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP
);
COMMENT ON TABLE T_WS_LOG IS '  HTTP- (ORDS)';
COMMENT ON COLUMN T_WS_LOG.id_log IS '';
COMMENT ON COLUMN T_WS_LOG.message IS ' ';
COMMENT ON COLUMN T_WS_LOG.request_header IS ' ';
COMMENT ON COLUMN T_WS_LOG.request_body IS ' ';
COMMENT ON COLUMN T_WS_LOG.response_header IS ' ';
COMMENT ON COLUMN T_WS_LOG.response_body IS ' ';
COMMENT ON COLUMN T_WS_LOG.dtz_log IS '/  ';
ALTER TABLE T_WS_LOG ADD CONSTRAINT PK_T_WS_LOG PRIMARY KEY (ID_LOG) USING INDEX;

CREATE OR REPLACE PACKAGE PK_ORDS_API IS
 
 
FUNCTION blob2clob
    ( a_blob            BLOB
    , a_from_charset    VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    , a_to_charset      VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    ) RETURN            CLOB;
 
 
PROCEDURE process_request
    ( a_request     CLOB
    );
 
 
END PK_ORDS_API;
/
 
CREATE OR REPLACE PACKAGE BODY PK_ORDS_API IS
 
 
FUNCTION blob2clob
    ( a_blob            BLOB
    , a_from_charset    VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    , a_to_charset      VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    ) RETURN            CLOB
AS
    l_clob      CLOB;
    l_amount    NUMBER := 2000;
    l_offset    NUMBER := 1;
    l_buffer    VARCHAR2(32767);
    l_length    PLS_INTEGER := dbms_lob.getlength(a_blob);
BEGIN
    dbms_lob.createtemporary(l_clob, TRUE);
    dbms_lob.OPEN(l_clob, dbms_lob.lob_readwrite);
    WHILE l_offset <= l_length LOOP
        l_buffer := UTL_RAW.cast_to_varchar2(UTL_RAW.convert( r            => dbms_lob.substr(a_blob, l_amount, l_offset)
                                                            , from_charset => a_from_charset
                                                            , to_charset   => a_to_charset));
        IF LENGTH(l_buffer) > 0 THEN
            dbms_lob.writeappend(l_clob, LENGTH(l_buffer), l_buffer);
        END IF;
        l_offset := l_offset + l_amount;
        EXIT WHEN l_offset > l_length;
    END LOOP;
    RETURN l_clob;
END blob2clob;
 
 
PROCEDURE process_request
    ( a_request     CLOB
    )
AS
    TYPE TStringHash IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(256);
    lh_hdr              TStringHash;
    l_hdr               VARCHAR2(256);
    l_resp              CLOB;
    l_response_status   INTEGER := 200;
    l_ccontent_type     VARCHAR2(64) := 'application/json';
    l_in_headers        VARCHAR2(32767);
BEGIN
    --      
    lh_hdr('SERVER_SOFTWARE')       := OWA_UTIL.get_cgi_env('SERVER_SOFTWARE');
    lh_hdr('SERVER_NAME')           := OWA_UTIL.get_cgi_env('SERVER_NAME');
    lh_hdr('GATEWAY_INTERFACE')     := OWA_UTIL.get_cgi_env('GATEWAY_INTERFACE');
    lh_hdr('SERVER_PROTOCOL')       := OWA_UTIL.get_cgi_env('SERVER_PROTOCOL');
    lh_hdr('SERVER_PORT')           := OWA_UTIL.get_cgi_env('SERVER_PORT');
    lh_hdr('REQUEST_METHOD')        := OWA_UTIL.get_cgi_env('REQUEST_METHOD');
    lh_hdr('PATH_INFO')             := OWA_UTIL.get_cgi_env('PATH_INFO');
    lh_hdr('PATH_TRANSLATED')       := OWA_UTIL.get_cgi_env('PATH_TRANSLATED');
    lh_hdr('SCRIPT_NAME')           := OWA_UTIL.get_cgi_env('SCRIPT_NAME');
    lh_hdr('QUERY_STRING')          := OWA_UTIL.get_cgi_env('QUERY_STRING');
    lh_hdr('REMOTE_HOST')           := OWA_UTIL.get_cgi_env('REMOTE_HOST');
    lh_hdr('REMOTE_ADDR')           := OWA_UTIL.get_cgi_env('REMOTE_ADDR');
    lh_hdr('AUTH_TYPE')             := OWA_UTIL.get_cgi_env('AUTH_TYPE');
    lh_hdr('REMOTE_USER')           := OWA_UTIL.get_cgi_env('REMOTE_USER');
    lh_hdr('REMOTE_IDENT')          := OWA_UTIL.get_cgi_env('REMOTE_IDENT');
    lh_hdr('CONTENT-TYPE')          := OWA_UTIL.get_cgi_env('CONTENT-TYPE');
    lh_hdr('CONTENT-LENGTH')        := OWA_UTIL.get_cgi_env('CONTENT-LENGTH');
    lh_hdr('HTTP_ACCEPT')           := OWA_UTIL.get_cgi_env('HTTP_ACCEPT');
    lh_hdr('HTTP_ACCEPT_LANGUAGE')  := OWA_UTIL.get_cgi_env('HTTP_ACCEPT_LANGUAGE');
    lh_hdr('HTTP_USER_AGENT')       := OWA_UTIL.get_cgi_env('HTTP_USER_AGENT');
    lh_hdr('HTTP_COOKIE')           := OWA_UTIL.get_cgi_env('HTTP_COOKIE');
 
    l_hdr := lh_hdr.FIRST;
    WHILE l_hdr IS NOT NULL LOOP
        IF lh_hdr(l_hdr) IS NOT NULL THEN
            l_in_headers := l_in_headers||CHR(10)||l_hdr||': '||lh_hdr(l_hdr);
        END IF;
        l_hdr := lh_hdr.NEXT(l_hdr);
    END LOOP;
 
    l_resp := '{ "result" : "success" }';
 
    INSERT INTO t_ws_log
        ( message
        , request_header
        , request_body
        , response_header
        , response_body)
    VALUES
        ( NULL
        , l_in_headers
        , a_request
        , 'Content-Type: '||l_ccontent_type
        , l_resp
        );
 
    OWA_UTIL.STATUS_LINE(nstatus => l_response_status, bclose_header => FALSE);
    OWA_UTIL.MIME_HEADER(ccontent_type => l_ccontent_type, bclose_header => FALSE);
    OWA_UTIL.HTTP_HEADER_CLOSE();
    htp.p(l_resp);
END process_request;
 
END PK_ORDS_API;
/

-- Generated by Oracle SQL Developer REST Data Services 18.1.0.095.1630
-- Exported REST Definitions from ORDS Schema Version 18.4.0.r3541002
-- Schema: WS_TEST   Date: Wed Oct 23 20:19:54 MSK 2019
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'WS_TEST',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'ws_test',
      p_auto_rest_auth      => TRUE);    
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'shop',
      p_base_path      => '/shop/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'shop',
      p_pattern        => 'order',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'shop',
      p_pattern        => 'order',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    l_blob BLOB := :body;
BEGIN
    PK_ORDS_API.process_request(a_request => PK_ORDS_API.blob2clob(l_blob));
EXCEPTION
    WHEN OTHERS THEN
        OWA_UTIL.STATUS_LINE(nstatus => 500, bclose_header => FALSE);
        OWA_UTIL.MIME_HEADER(ccontent_type => ''application/json'');
        htp.p(''{ "result" : "error", "message" : "''||SQLERRM||''" }'');
END;'
      );
 
 
  COMMIT; 
END;

PROCEDURE process_request
    ( a_request     CLOB
    , a_prm_uri     VARCHAR2 := NULL
    , a_prm_hdr     VARCHAR2 := NULL
    )
AS
    TYPE TStringHash IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(256);
    lh_hdr              TStringHash;
    l_hdr               VARCHAR2(256);
    l_resp              CLOB;
    l_response_status   INTEGER := 200;
    l_ccontent_type     VARCHAR2(64) := 'application/json';
    l_in_headers        VARCHAR2(32767);
BEGIN
    --      
    lh_hdr('SERVER_SOFTWARE')       := OWA_UTIL.get_cgi_env('SERVER_SOFTWARE');
    lh_hdr('SERVER_NAME')           := OWA_UTIL.get_cgi_env('SERVER_NAME');
    lh_hdr('GATEWAY_INTERFACE')     := OWA_UTIL.get_cgi_env('GATEWAY_INTERFACE');
    lh_hdr('SERVER_PROTOCOL')       := OWA_UTIL.get_cgi_env('SERVER_PROTOCOL');
    lh_hdr('SERVER_PORT')           := OWA_UTIL.get_cgi_env('SERVER_PORT');
    lh_hdr('REQUEST_METHOD')        := OWA_UTIL.get_cgi_env('REQUEST_METHOD');
    lh_hdr('PATH_INFO')             := OWA_UTIL.get_cgi_env('PATH_INFO');
    lh_hdr('PATH_TRANSLATED')       := OWA_UTIL.get_cgi_env('PATH_TRANSLATED');
    lh_hdr('SCRIPT_NAME')           := OWA_UTIL.get_cgi_env('SCRIPT_NAME');
    lh_hdr('QUERY_STRING')          := OWA_UTIL.get_cgi_env('QUERY_STRING');
    lh_hdr('REMOTE_HOST')           := OWA_UTIL.get_cgi_env('REMOTE_HOST');
    lh_hdr('REMOTE_ADDR')           := OWA_UTIL.get_cgi_env('REMOTE_ADDR');
    lh_hdr('AUTH_TYPE')             := OWA_UTIL.get_cgi_env('AUTH_TYPE');
    lh_hdr('REMOTE_USER')           := OWA_UTIL.get_cgi_env('REMOTE_USER');
    lh_hdr('REMOTE_IDENT')          := OWA_UTIL.get_cgi_env('REMOTE_IDENT');
    lh_hdr('CONTENT-TYPE')          := OWA_UTIL.get_cgi_env('CONTENT-TYPE');
    lh_hdr('CONTENT-LENGTH')        := OWA_UTIL.get_cgi_env('CONTENT-LENGTH');
    lh_hdr('HTTP_ACCEPT')           := OWA_UTIL.get_cgi_env('HTTP_ACCEPT');
    lh_hdr('HTTP_ACCEPT_LANGUAGE')  := OWA_UTIL.get_cgi_env('HTTP_ACCEPT_LANGUAGE');
    lh_hdr('HTTP_USER_AGENT')       := OWA_UTIL.get_cgi_env('HTTP_USER_AGENT');
    lh_hdr('HTTP_COOKIE')           := OWA_UTIL.get_cgi_env('HTTP_COOKIE');
    lh_hdr('a_prm_uri')             := a_prm_uri;
    lh_hdr('a_prm_hdr')             := a_prm_hdr;
 
    l_hdr := lh_hdr.FIRST;
    WHILE l_hdr IS NOT NULL LOOP
        IF lh_hdr(l_hdr) IS NOT NULL THEN
            l_in_headers := l_in_headers||CHR(10)||l_hdr||': '||lh_hdr(l_hdr);
        END IF;
        l_hdr := lh_hdr.NEXT(l_hdr);
    END LOOP;
 
    l_resp := '{ "result" : "success" }';
 
    INSERT INTO t_ws_log
        ( message
        , request_header
        , request_body
        , response_header
        , response_body)
    VALUES
        ( NULL
        , l_in_headers
        , a_request
        , 'Content-Type: '||l_ccontent_type
        , l_resp);
 
    OWA_UTIL.STATUS_LINE(nstatus => l_response_status, bclose_header => FALSE);
    OWA_UTIL.MIME_HEADER(ccontent_type => l_ccontent_type, bclose_header => FALSE);
    OWA_UTIL.HTTP_HEADER_CLOSE();
    htp.p(l_resp);
END process_request;

PUT http://<server>:<port>/ords/ws_test/t_order/25 HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: application/json;charset=UTF-8
Content-Length: 157
Host: <server>:<port>
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)
 
{
	"num" : "ords-3472634",
	"buyer_name" : "Buyer Name",
	"dt_order" : "2019-10-25T12:00:00Z",
	"memo" : "  1"
}

HTTP/1.1 200 OK
Content-Type: application/json
Content-Location: http://<server>:<port>/ords/ws_test/t_order/25
ETag: "..."
Transfer-Encoding: chunked
 
{
   "id_order": 25,
   "num": "ords-3472634",
   "buyer_name": "Buyer Name",
   "dt_order": "2019-10-25T12:00:00Z",
   "memo": "  1",
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/item"
      },
            {
         "rel": "collection",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}

HTTP/1.1 200 OK
Date: Fri, 25 Oct 2019 15:39:58 GMT
Content-Type: application/json
ETag: "..."
Transfer-Encoding: chunked
 
{
   "items": [   {
      "id_order": 25,
      "num": "ords-3472634",
      "buyer_name": "Buyer Name",
      "dt_order": "2019-10-25T12:00:00Z",
      "memo": "ўµЃ‚ѕІ‹№ ·°є°· 1.  ·јµЅ‚Њ їЂјµ‡°Ѕµ",
      "links": [      {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      }]
   }],
   "hasMore": false,
   "limit": 25,
   "offset": 0,
   "count": 1,
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/"
      },
            {
         "rel": "first",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}

HTTP/1.1 200 OK
Date: Fri, 25 Oct 2019 15:44:35 GMT
Content-Type: application/json
ETag: "..."
Transfer-Encoding: chunked
 
{
   "id_order": 25,
   "num": "ords-3472634",
   "buyer_name": "Buyer Name",
   "dt_order": "2019-10-25T12:00:00Z",
   "memo": "ўµЃ‚ѕІ‹№ ·°є°· 1.  ·јµЅ‚Њ їЂјµ‡°Ѕµ",
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/item"
      },
            {
         "rel": "collection",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}