HTTP requests via Oracle

1. Add ACL connection via SYSDBA:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test_acl.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'DBSCHEMA',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl.xml',
    host        => '192.168.*.*', 
    lower_port  => 1,
    upper_port  => 9999); 
END;

where you should apply desired IP address and DB UserName (Schema), which will use it.

2. Check it with next select statement:

SELECT host,
       lower_port,
       upper_port,
       ace_order,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date,
       grant_type,
       inverted_principal,
       principal,
       principal_type,
       privilege
FROM   dba_host_aces
ORDER BY host, ace_order;
SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
ORDER BY acl, principal, privilege;

3. Now you can create a function,/procedure which will use this ACL as a connection and retrieve data as a plain text:

 
   TYPE REFCUR IS REF CURSOR; 

DECLARE
    res refcur;  
         
    l_req utl_http.req;
    l_resp utl_http.resp;
   
    resul_1 pls_integer := 0;  
    phr_1 varchar2(1000) := '';  
    
    CHECK_host CONSTANT varchar2(30) := '12.11.12.11/'; 

    PROCEDURE server_is_available(host_ch IN varchar2, avail OUT integer, phras OUT varchar2) as 
        l_req_s utl_http.req;
        l_resp_s utl_http.resp; 
        utl_err varchar2(1000);
      BEGIN
            l_req_s  := utl_http.begin_request(host_ch);      
            l_resp_s := utl_http.get_response(l_req_s);  
 
            avail := l_resp_s.status_code;   
            phras := l_resp_s.reason_phrase;

            utl_http.end_response(l_resp_s);  
            dbms_output.put_line('      Response: '||avail);
       EXCEPTION
            WHEN OTHERS THEN 
                      UTL_HTTP.END_RESPONSE(l_resp_s); 
                      utl_err:= Utl_Http.Get_Detailed_Sqlerrm;   
                     dbms_output.put_line('      Response: -1');
                     dbms_output.put_line('      '||host_ch||' connection has been failed: '||utl_err);
                     avail := -1; 
                     phras := 'FAIL';               
      END; 
  
    PROCEDURE LOCAL_DUAL_REF(REFC OUT refcur, host_ch IN varchar2 := '', avail_1 IN integer := 0, phras_1 IN varchar2 := '') AS 
    BEGIN
        OPEN REFC FOR
            SELECT host_ch adr, avail_1 av, phras_1 ph from dual;
    END;   
 
  BEGIN
              dbms_output.put_line('Close previous connection if it is...');
          utl_http.end_response(l_resp);                 
          
              dbms_output.put_line('Start host checking... ' || CHECK_host); 
          server_is_available(CHECK_host,resul_1,phr_1);   
           
              dbms_output.put_line('Close connection...');
          utl_http.end_response(l_resp);
          
          LOCAL_DUAL_REF(RES, resul_1, phr_1); 
          RETURN RES;
          
  EXCEPTION WHEN utl_http.end_of_body THEN 
                            utl_http.end_response(l_resp);   
                            LOCAL_DUAL_REF(RES, -1); 
                            RETURN RES;
          WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN 
                            utl_http.end_response(l_resp);
                            LOCAL_DUAL_REF(RES, -2); 
                            RETURN RES;
          WHEN others THEN 
                            LOCAL_DUAL_REF(RES); 
                            RETURN RES;
  END;

4. Don’t forget to close the connection. Otherwise, you will get them too many and the function/procedure will not work for some time.


1. You can change default timeout time (default is 60 seconds) for connection establishing

UTL_HTTP.set_transfer_timeout(300);

2. You can see response as a web-page in plain text (HTML, JS, CSS):

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

if(l_resp.status_code = 200 and l_resp.reason_phrase = 'OK') then loop
      utl_http.read_text(l_resp, l_text, 32766);
      dbms_output.put_line(l_text);  
end if;

3. You can add proxy settings

UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');

4. You can add Headers:

UTL_HTTP.SET_HEADER(l_req, 'User-Agent', 'Mozilla/4.0');
l_resp := UTL_HTTP.GET_RESPONSE(l_req);

FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(l_resp) LOOP
    UTL_HTTP.GET_HEADER(l_resp, i, name, value);
    DBMS_OUTPUT.PUT_LINE(name || ': ' || value);
END LOOP;

 


Optional:

1. You can add DB UserName to the ACL which exists already:

 DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '192.168.*.*', 
    lower_port => 1,
    upper_port => 9999,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'DBSCHEMA_2',
                              start_date => SYSTIMESTAMP,
                              principal_type => xs_acl.ptype_db)); 

2. You can remove it as well:

  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'test_acl.xml',
        principal   => 'DBSCHEMA_2');

3. Also you can remove entire ACL:

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'test_acl.xml');

 

Leave a Reply