Build a sql query to get sku, description and quantity through JSON API by using UTL_HTTP

Before you panic, I will help you with the methods. A ton of information is available online. StoreHippo is a SAAS E-commerce platform and below is help on their API information: [login to view URL] I have oracle 11g on Linux, what I want to do is retrieve all the products in a row format, each line with a product and just 3 columns (SKU, description, and quantity) Below is the functioning SQL query and I have already added the access key, it gives the complete product data. The Job: modify the SQL query to get the SKU, description, and quantity in a tabular format, each product in a row. SQL Query beings: /* Formatted on 12/11/2020 10:23:31 AM (QP5 v5.114.809.3010) */ DECLARE t_http_req [login to view URL]; t_http_resp [login to view URL]; t_request_body varchar2 (30000); t_respond varchar2 (30000); t_start_pos integer := 1; t_output varchar2 (2000); BEGIN /*Construct the information you want to send to the webservice. Normally this would be in a xml structure. But for a REST- webservice this is not mandatory. The webservice i needed to call excepts plain test.*/ t_request_body := 'I dont know what to write here'; /*Telling Oracle where the webservice can be found, what kind of request is made and the version of the HTTP*/ t_http_req := UTL_HTTP.begin_request ( '[login to view URL]', 'GET', 'HTTP/1.1' ); /*In my case the webservice used authentication with a username an password that was provided to me. You can skip this line if it's a public webservice.*/ UTL_HTTP.set_authentication (t_http_req, 'access-key', '6a3b8566d83da3f1037164f42cbd10e2'); /*Describe in the request-header what kind of data is send*/ UTL_HTTP.set_header (t_http_req, 'Content-Type', 'text/xml charset=UTF-8'); /*Describe in the request-header the lengt of the data*/ UTL_HTTP.set_header (t_http_req, 'Content-Length', LENGTH (t_request_body)); /*Put the data in de body of the request*/ UTL_HTTP.write_text (t_http_req, t_request_body); /*make the actual request to the webservice en catch the responce in a variable*/ t_http_resp := UTL_HTTP.get_response (t_http_req); /*Read the body of the response, so you can find out if the information was received ok by the webservice. Go to the documentation of the webservice for what kind of responce you should expect. In my case it was: <responce> <status>ok</status> </responce> */ UTL_HTTP.read_text (t_http_resp, t_respond); DBMS_OUTPUT.put_line (t_respond); /*Some closing?1 Releasing some memory, i think....*/ UTL_HTTP.end_response (t_http_resp); END; More Information: If you have a problem accessing SSL data through oracle and it requires oracle wallet, there is a workaround as follows: Just add this to [login to view URL]: SSLProxyEngine on ProxyPass /my-selected-merchant-site [login to view URL] ProxyPassReverse /my-selected-merchant-site [login to view URL] *make sure that you have mod_ssl installed (with default options) and “ProxyRequests Off” After that, change the package from this: utl_http.begin_request(‘[login to view URL]’,’POST’,’HTTP/1.1′); to this: utl_http.begin_request(‘http://myhost/my-selected-merchant-site/’,’POST’,’HTTP/1.0′); And forget about the wallet.