<result>
    <count>1</count>
    <status>0</status>
    <statusCode>201</statusCode>
    <response>
        <token>oAf5U23dQqn0cSQzNuOwN7CLW7R+fik4xV5Te7/YZ9IERMivUwUiQA9abO2U+maXGgygOwIZtLoLWFN04q1k4AV0+vGOsJiQadjt+4iOq0vs5mNImH425DsknhPsN9qA1N</token>
        <expires>2017-09-13T09:47:55.0166204+03:00</expires>
        <sms>false</sms>
        <phoneNoise />
    </response>
</result>

CREATE OR REPLACE PACKAGE WS_TOOLS
    IS
    FUNCTION callWS(p_url IN VARCHAR2,p_method IN XMLTYPE,p_user in VARCHAR2 :=null,p_pass in VARCHAR2 :=null) RETURN XMLTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY WS_TOOLS
IS


--internal function to post http(s) request
FUNCTION httpPost
(
    p_url     IN VARCHAR2,
    p_data    IN CLOB,
    p_timeout IN BINARY_INTEGER DEFAULT 60
) 
    RETURN CLOB
IS
    --
    v_request  utl_http.req;
    v_response utl_http.resp;
    v_buffer   CLOB;
    v_chunk    VARCHAR2(4000);
    v_length   NUMBER;
    v_index    NUMBER;
BEGIN

    v_index := 1;
    v_length := nvl(length(p_data), 0);

    -- configure HTTP
    utl_http.set_response_error_check(enable => FALSE);
    utl_http.set_detailed_excp_support(enable => FALSE);
    utl_http.set_transfer_timeout(p_timeout);

    -- send request
    v_request := utl_http.begin_request(p_url, 'POST','HTTP/1.1');
    utl_http.set_header(v_request, 'Content-Type', 'text/xml');
    --utl_http.set_header(v_request, 'Content-Length', v_length);
    WHILE v_index <= v_length LOOP
        utl_http.write_text(v_request, substr(p_data, v_index, 4000));
        v_index := v_index + 4000;
    END LOOP;

    -- check HTTP status code for error
    IF v_response.status_code <> utl_http.http_ok THEN   
        raise_application_error(-20400,v_response.status_code || ' - ' || v_response.reason_phrase);
    END IF;

    -- get response
    dbms_lob.createtemporary(v_buffer, FALSE);
    v_response := utl_http.get_response(v_request);
    BEGIN
        LOOP
            utl_http.read_text(v_response, v_chunk, 4000);
            dbms_lob.writeappend(v_buffer, length(v_chunk), v_chunk);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN NULL;
    END;
    utl_http.end_response(v_response);

    RETURN v_buffer;

END;


FUNCTION callWS
(
    p_url IN VARCHAR2,
    p_method IN XMLTYPE,
    p_user in VARCHAR2 :=null,
    p_pass in VARCHAR2 :=null

    --p_timeout IN NUMBER := 60
)
    RETURN XMLTYPE
IS

v_envelope XMLTYPE;
    v_header XMLTYPE;
    v_response CLOB;
    v_fault XMLTYPE;
    v_sqlerrm VARCHAR2(2000);
BEGIN
    if p_user is not null then
        --prepare header
        SELECT
XMLElement(
                "soap:Header",
                XMLElement(
                    "wsse:Security",
                    XMLAttributes(
                        'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:wsse"
                    ),
                    XMLElement(
                        "wsse:UsernameToken",
                        XMLAttributes(
                            'UsernameToken-1' AS "wsu:Id",
                            'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:wsu"
                        ),
                        XMLElement(
                            "wsse:Username",
                            'cfront'
                        ),
                        XMLElement(
                            "wsse:Password",
                            XMLAttributes(
                                'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText' AS "Type"
                            ),
                            'cfrontpass'
                        )
                    )
                )
            )

 SELECT
        XMLElement(
            "soap:Envelope",
            XMLAttributes(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soap"
            ),
            v_header,
            
            XMLElement(
                "soap:Body",
                p_method
            )
        )
    INTO
        v_envelope
    FROM
        dual;

    -- POST request
    v_response := httpPost(
        p_url,
        v_envelope.getClobVal(),
        60 -- 60 seconds
    );
    IF v_response IS NULL THEN
        raise_application_error(-20400,'empty response');
    END IF;

    -- parse response
    BEGIN
        v_envelope := XMLType(v_response);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error(-20400,'can''t parse xml: '||SQLERRM);
    END;

    -- check for a fault
    v_fault := v_envelope.extract(  
        '/soap:Envelope/soap:Body/soap:Fault', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
    IF v_fault IS NOT NULL THEN
        v_sqlerrm := v_fault.extract('.//faultstring/text()').getStringVal();
        raise_application_error(-20400,'remote error: '||v_sqlerrm);
    END IF;

    -- the actual response is the child of the "soap:Body" element
    RETURN v_envelope.extract(
        '/soap:Envelope/soap:Body/*[position() = 1]', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
END;

END;

create or replace procedure publish_cinema_event
( p_username in varchar2
, p_password in varchar2
)is
  req utl_http.req;
  res utl_http.resp;
  --url varchar2(4000) := 'http://alsytto.onlyoffice.eu/api/2.0/authentication.xml?userName=roma@alsy.by&;password=cjdCtrhtnyj';
  url varchar2(4000) := 'date.jsontest.com';
  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '{}';
 
begin
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
 -- utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end publish_cinema_event;

content varchar2(4000) := '{}';

create or replace procedure publish_cinema_even
is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'https://alsytto.onlyoffice.eu/api/2.0/authentication.xml?userName=roma@roma.by&;password=password';
  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '{}';
 
begin
  utl_http.set_wallet ('file:c:\app\wallet2','test1234');
  utl_http.set_detailed_excp_support (true);
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  
  --UTL_HTTP.set_wallet('file:/c:\app\oraUser\product\12.1.0\dbhome_1\BIN\wallets', 'test1234');
  --utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  --utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin 
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end publish_cinema_even;

<result><count>1</count><status>0</status><statusCode>201</statusCode><response><token>oAf5U23dQqn0cSQzNuOwN7CLW7R+fiv4xV5Te7/YZ9IERMivUwUiQA9abO2U+maXGgygOwIZtLoLWFN04q1k4AV0+vGOsJiQadjt+4iOq0vs5mNImH5DsknhPsN9qA1N</token><expires>2017-09-14T10:53:56.2760087+03:00</expires><sms>false</sms><phoneNoise /></response></result>

(SELECT * FROM (
   SELECT PERSON_EMAIL FROM PERSONS  WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC)  WHERE  rownum=1);

EMAIL := (SELECT * FROM (
   SELECT PERSON_EMAIL FROM PERSONS  WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC)  WHERE  rownum=1);

SET EMAIL = SELECT * FROM (
   SELECT PERSON_EMAIL FROM PERSONS  WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC)  WHERE  rownum=1;

email varchar2(4000);