declare 

      l_url VARCHAR2(4000) := utl_url.escape('https://api-test.X.com/partner/retail/poi/v3/stXX'); 

      l_http_request UTL_HTTP.req;

      l_http_response UTL_HTTP.resp;

      l_response CLOB;

      l_access_token VARCHAR2(2000);

      -- 

      v_msg                VARCHAR2(32000);

     v_entire_msg         CLOB;

      v_body               VARCHAR2(32000);

      v_concat             NUMBER := 0;

      --parameters

      p_country            VARCHAR2(3):='X';

      p_limit              NUMBER := 1;

     --

      l_escaped_url VARCHAR2(4000);

      l_debug NUMBER;

    BEGIN

      --  Step 1: Get access token using function

      l_access_token :=  get_access_token;

     -- Step 2: Get url using filled parameters

        -- parameters

     IF p_country IS NOT NULL THEN

                l_url := l_url

                        || '?country='

                        || utl_url.escape(p_country);

               v_concat := 1;

      END IF;

      IF p_limit IS NOT NULL THEN

                IF v_concat = 0 THEN

                   l_url := l_url

                            || '?limit='

                            || p_limit;

                   v_concat := 1;

                ELSIF v_concat = 1 THEN

                   l_url := l_url

                            || chr(38)

                            || 'limit='

                            || p_limit;

                END IF;

      END IF;

     DBMS_OUTPUT.put_line('url: ' ||l_url); 

      l_escaped_url := utl_url.escape(l_url);

     DBMS_OUTPUT.put_line('l_escaped_url: ' ||l_escaped_url); 

      --Step 3: call REST API using l_url

      l_http_request := UTL_HTTP.begin_request(l_escaped_url, 'GET', 'HTTP/1.1');

     UTL_HTTP.SET_HEADER(l_http_request, 'Authorization', 'Bearer ' || l_access_token);

     UTL_HTTP.SET_HEADER(l_http_request, 'content-type', 'application/json; charset=utf-8'); 

     UTL_HTTP.SET_HEADER(l_http_request, 'Cache-Control', 'no-cache'); 

     UTL_HTTP.SET_HEADER(l_http_request, 'Connection', 'keep-alive');

     UTL_HTTP.SET_HEADER(l_http_request, 'User-Agent', 'PostmanRuntime/7.39.1');

     --UTL_HTTP.SET_HEADER(l_http_request, 'Accept-Encoding', 'gzip, deflate, br');--return broken message

     UTL_HTTP.SET_HEADER(l_http_request, 'Accept', '*/*');

      --

      l_http_response := UTL_HTTP.get_response(l_http_request);

     UTL_HTTP.SET_BODY_CHARSET(r=> l_http_response, charset=>'UTF-8');

      --Step 4: get response full

            IF l_http_response.status_code = 200 --SUCCESFUL OPERATION

             THEN

                BEGIN

                LOOP

                   utl_http.read_text(r => l_http_response, data => v_msg);

                   v_entire_msg := v_entire_msg || v_msg;         

                END LOOP;

             EXCEPTION

               WHEN utl_http.end_of_body THEN

                  NULL;

                END; 

    utl_http.end_response(l_http_response);

    --Step 5: Insert data

           INSERT INTO T_JSON_DOC values (api_json_seq.nextval,p_country, v_entire_msg, sysdate);

           COMMIT;

            ELSIF l_http_response.status_code != 200 THEN

                 DBMS_OUTPUT.put_line ( 'Reason phrase: ' || l_http_response.reason_phrase);

            END IF;

   UTL_HTTP.END_RESPONSE(l_http_response); 

    EXCEPTION

      WHEN UTL_HTTP.TOO_MANY_REQUESTS 

           THEN

               UTL_HTTP.END_RESPONSE(l_http_response);    

      WHEN OTHERS THEN

       UTL_HTTP.end_response(l_http_response);

        RAISE;

    END;