http request failed

Chandler Bing

hello everyone,

I need little help on utl_htp. my requirement is capture weather information of current date, so i create a procedure that return all information through api weather bit as you seen below code:

create or replace NONEDITIONABLE procedure   GET_WEATHER_DATA

( p_loc_code in varchar2,

 p_loc_name in varchar2,

 p_lat in varchar2,

 p_long in varchar2

) is

req utl_http.req;

res utl_http.resp;

url varchar2(4000) := 'http://api.weatherbit.io/v2.0/current?&lat='||p_lat||'&lon='||p_long||'&key=7cffe6ab7f734aefb1a511a9d26ea520';

name varchar2(4000);

buffer varchar2(4000);

content varchar2(4000);

v_location varchar2(400);

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 GET_WEATHER_DATA;

I run this code from sys user and it will run perfectly in my local system and i m using database 12.2c

but the problem is that when i run this code on another machine that also have same database version this will show below error:

I dont understand where is the problem.

and one more thinng in my local i dont create any acl then it will worked fine but in another system database i also created acl and give grant utl htp to sys this will show same error.

Can anyone help me?

Billy Verreynne

It is a fundamental security violation to create user code and user objects in the SYS schema. DON'T!

Using DBMS_OUTPUT as a data buffer is also a basic mistake. DBMS_OUTPUT is primarily used as an application debug trace buffer - not for passing data from the server to the calling application. If XML is returned by the web service, return a XMLTYPE data type. Else return a LOB. Alternatively parse the response in your code and return a data structure.

Does the other server have access to the Internet URL used?

Chandler Bing

Hi billy,

thanks for reply, first of all this is testing code and onther user htp_utl dosent work it will show same i dont know the solution of error that the reason create in sys schema .

Dont worry about the buffer and it will return json format i will parse this data and store in database table.

i only need to know that why utl http request is failed and yes server have access to the internet url.

Chandler Bing

hi @fac586 can you help me in this?

Billy Verreynne

Try the following SQL and post the results:

select 
   httpuritype(
      'http://api.weatherbit.io/v2.0/current?&lat=26.8467&lon=80.9462&key=7cffe6ab7f734aefb1a511a9d26ea520'
   ).getCLOB() 
from dual;

Chandler Bing

Hi,

Thanx for your reply ,I got below results:

But i dont understand why is this given certification error, I am run same command in my local it will give correct result as shown below image

why?

Billy Verreynne

A HTTPS web server has a certificate that is used for identification and encryption.

The web browser needs to accept and trust this server certificate.

On Oracle, the Root Authority and Certification Authority certificates that signed the web server certificate, are required to be in an Oracle wallet as trusted certificates - and this wallet to be used with UTL_HTTP.

Your databases have a valid wallet in one case, and no wallet in the other case.