Consuming rest API using PL/SQL - Body not recognized

Question

I am new to this forum and consuming Rest API's using PL/SQL. I am trying to consume a rest service from pl/sql, but the server is not able to identify the request. The same request works from postman.. so no problem in the service, something with my pl/sql code i think..

I am trying the free api provided by rapidapi. https://rapidapi.com/weatherapi/api/weatherapi-com

My Stored Procedure

Create Or Replace Procedure TEST_REST_API
(
  PURL IN VARCHAR2
)

is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := PURL;
  buffer varchar2(32767);
  content varchar2(8000);
begin
  dbms_output.put_line('START'); 
  UTL_TCP.close_all_connections;
  DBMS_OUTPUT.put_line('2');
  UTL_HTTP.set_wallet('file:/u01/app/oracle/product/11.2.0/db_1/wallet/rapidapi','wallet123');
  DBMS_OUTPUT.put_line('3');
  UTL_HTTP.CLEAR_COOKIES();
  content := '{"q":"autp:ip"}';
  --content := '';
  dbms_output.put_line('content '||content);
  req := utl_http.begin_request(url, 'GET',' HTTP/1.1');
  utl_http.set_header(req, 'X-RapidAPI-Key', 'af1e7931bamsh3ac102afa8fef68p100423jsn8d4d3cc1325b');
  utl_http.set_header(req, 'X-RapidAPI-Host', 'weatherapi-com.p.rapidapi.com');
  utl_http.set_header(req, 'Content-Length', length(content));
  utl_http.set_header(req, 'User-Agent', 'mozilla/4.0'); 
  --utl_http.set_header(req, 'user-agent', 'PostmanRuntime/7.29.2'); 
  utl_http.set_header(req, 'Content-Type', 'application/json');
  utl_http.set_header(req, 'Connection','keep-alive');
  --utl_http.set_header(req, 'Accept','*/*');
  --utl_http.write_text(req, content);
  utl_http.write_text(req, content);
  --utl_http.write_text(req,'');
  --insert into wstemp values (req);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer,TRUE);
      dbms_output.put_line(buffer);
    end loop;
    dbms_output.put_line('Response XML:'|| cast(x_clob as varchar2));
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end;

The execution script

BEGIN
TEST_REST_API
(
  'https://weatherapi-com.p.rapidapi.com/ip.json'
);
END;
/

The response from server

{"error":{"code":1003,"message":"Parameter q is missing."}}

Any help would be highly appreciated.

Answer 1

If you're posting data then you need to use POST not GET:

req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');

You also seem to have a typo in your content, with autp instead of auto:

 content := '{"q":"auto:ip"}';

---

However, the API you're using only seems to support GET, so you can't send body content - that only applies when posting data. You will need to revert the method to GET, remove content from your test procedure, and either append the request parameter in the procedure:

req := utl_http.begin_request(url || '?q=auto:ip', 'GET',' HTTP/1.1');

or modify the URL being passed in, or add another parameter with the query parameter to append. You may need to add quotes, and might also need to escape some entities.