0

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.

Faheem Ahmed Shaik
  • 1

1 Answers1

0

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318