Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marcelo Reis.

Asked: February 16, 2021 - 2:26 pm UTC

Last updated: February 17, 2021 - 4:33 pm UTC

Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Viewed 100+ times

You Asked

Hi,

Could you help-me please. I am tryed to execute a simple command and does not work.

SELECT utl_http.request(' http://www.oracle.com/' ) FROM dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1

I tryed in cloud anvironment and dos not work too. I am conected as Admin;

ORA-01031: insufficient privileges ORA-06512: at "SYS.UTL_HTTP", line 1810 ORA-06512: at "SYS.UTL_HTTP", line 136 ORA-06512: at "SYS.UTL_HTTP", line 1745 ORA-06512: at line 1



I want to do a interface using HTTP to send data to a IoT plataform.
when I comment the line where is the comand UTL_HTTP.begin_request don't occur error and show I have access to the package UTL_HTTP...

DECLARE
l_url VARCHAR2(50) := ' http://api.losant.com';
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
BEGIN
-- Make a HTTP request and get the response.
--l_http_request := UTL_HTTP.begin_request(l_url, 'GET', 'HTTP/1.1'); <<----------------- Why don't I have access?

--l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.end_response(l_http_response);
END;






and we said...

You don't query a UTL_HTTP request from dual.

Here's a simple fragment as an example of getting arbitrary length data from a http end point

set serverout on
declare
  l_url      varchar2(100) := '[your url]';
  l_req      utl_http.req;
  l_resp     utl_http.resp;
  l_content  clob;
  l_buffer   varchar2(32767);
begin
  dbms_lob.createtemporary(l_content, false);

  l_req  := utl_http.begin_request(l_url);
  l_resp := utl_http.get_response(l_req);

  begin
    loop
      utl_http.read_text(l_resp, l_buffer, 32000);
      dbms_lob.writeappend (l_content, length(l_buffer), l_buffer);
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_resp);
  end;

  dbms_output.put_line('Size='||length(l_content));

  dbms_lob.freetemporary(l_content);
end;
/



If you're looking at sending data to an IoT platform, they might be expecting you to make a REST call. If that's the case I'd suggest APEX_WEB_SERVICE

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_web_service.htm#AEAPI537

which takes care of a lot of the heavy lifting for you.

Similarly, if you are using https not http, then check out Tim's article on how to get that up and running

https://oracle-base.com/articles/misc/utl_http-and-ssl


Rating

  (3 ratings)

Comments

UTL_HTTP

Marcelo, February 17, 2021 - 2:58 pm UTC

Thanks a lot by tips.
When I exchange the host the procedure does work fine. But when put the host that I want to work occurs error. I think I have a problem with my firewall or something... To test I puted a host used to send e-mail and does work. Is there anything to do in Oracle? Or my problem is network...

1 declare
2 l_url varchar2(100) := 'XXXX'; <--- here I put IP e-mail to test
3 l_req utl_http.req;
4 l_resp utl_http.resp;
5 l_content clob;
6 l_buffer varchar2(32767);
7 begin
8 dbms_lob.createtemporary(l_content, false);
9 l_req := utl_http.begin_request(l_url);
10 l_resp := utl_http.get_response(l_req);
11 begin
12 loop
13 utl_http.read_text(l_resp, l_buffer, 32000);
14 dbms_lob.writeappend (l_content, length(l_buffer), l_buffer);
15 end loop;
16 exception
17 when utl_http.end_of_body then
18 utl_http.end_response(l_resp);
19 end;
20 dbms_output.put_line('Size='||length(l_content));
21 dbms_lob.freetemporary(l_content);
22* end;
23
24 /
Size=657

PL/SQL procedure successfully completed.

tks,

Chris Saxon
February 17, 2021 - 4:33 pm UTC

If you're sending email you should use utl_mail instead of utl_http:

https://oracle-base.com/articles/10g/utl_mail-send-email-from-the-oracle-database

If you want to check connectivity to remote hosts, logon to the database server and see if you can contact it from outside the database. If no, you have firewall/network configuration issues.

If you can, check that your access-control lists (ACLs) in the database are configured to allow you to reach other hosts

https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1

UTL_HTTP

Marcelo, February 17, 2021 - 5:22 pm UTC

Hi,

If you want to check connectivity to remote hosts, logon to the database server and see if you can contact it from outside the database. If no, you have firewall/network configuration issues.

Do you want to say to use PING <<IP>> , I did and does work fine;

If you can, check that your access-control lists (ACLs) in the database are configured to allow you to reach other hosts

I did all procedures ...


ACL PRINCIPAL PRIVILEGE IS_GR START_DATE END_DATE
------------------------------ ------------------------------ ------------------------------ ----- ----------- -----------
/sys/acls/teste.xml ATH connect true
/sys/acls/teste.xml ATH resolve true

UTL_HTTP

Marcelo, February 17, 2021 - 6:33 pm UTC

Hi,

I told with the support guys and my problem is the firewall. I can not request a host outside of the Cia (out VAN). I need to do a request to release a port and target IP to where I want to send data (IoT Plataform). Then first, I will try to release Port and IP and after I come back with my procedure PL/SQL.
Thank you very much;

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.