This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 3, 2013 10:02 PM by BillyVerreynne RSS

Problems with UTL_HTTP and network access problems

Brent Harlow Newbie
Currently Being Moderated

I have a similar problem to what has been listed on the following thread

 

https://forums.oracle.com/thread/2454508

 

But have been unable to come to any solution so need some more help !!

 

I have this all working fine from my local laptop but am not able to get it working from our test server (behind a layer 7 switch)

 

-- Testing for google.com
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'google.xml',
    description  => 'Google ACL Control List',
    principal    => 'TEST_USER',
    is_grant     => TRUE,
    privilege    => 'connect');
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'google.xml',
    host        => 'google.com');
END;
/
  COMMIT;
END;
/

 

Running the following as TEST_USER

 

SQL> select utl_http.request('google.com') from dual              
                                                                  
   1 select utl_http.request('google.com') from dual              
            *                                                     
ORA-29273: HTTP request failed                                    
ORA-06512: at "SYS.UTL_HTTP", line 1722                           
ORA-24247: network access denied by access control list (ACL)     
ORA-06512: at line 1                                              

 

which suggests that ACL is the problem, but if I use tcpportping function

 

SQL> select tcpportping('google.com',80) from dual   
                                                     
TCPPORTPING('GOOGLE.COM',80)                         
----------------------------                         
                           0                         
                                                     
1 row selected                                       
                                                     
Elapsed time: 00:00:00.424                           

 

It works fine.  If I tried tcpportping with a different host ...

 

SQL> select tcpportping('google.co.uk',80) from dual           
                                                               
   1 select tcpportping('google.co.uk',80) from dual           
            *                                                  
ORA-29260: network error: not connected                        
ORA-06512: at "SYS.UTL_TCP", line 212                          
ORA-06512: at "SYS.UTL_TCP", line 432                          
ORA-06512: at "TEST_USER.TCPPORTPING", line 47            
ORA-24247: network access denied by access control list (ACL)  
ORA-06512: at line 1                
                          

 

which suggests to me that ACL is not the real issue for "select utl_http.request('google.com') from dual" as tcpportping does not report an ACL issue but it (expectedly) does for a different host.

 

I can perform nslookup from DB o/s user

 

[oracle@test2 ~]$ nslookup google.com
Server:         8.8.8.8
Address:        8.8.8.8#53
Non-authoritative answer:
Name:   google.com
Address: 173.194.34.98
Name:   google.com
Address: 173.194.34.100
Name:   google.com
Address: 173.194.34.102
Name:   google.com
Address: 173.194.34.99
Name:   google.com
Address: 173.194.34.97
Name:   google.com
Address: 173.194.34.103
Name:   google.com
Address: 173.194.34.104
Name:   google.com
Address: 173.194.34.96
Name:   google.com
Address: 173.194.34.101
Name:   google.com
Address: 173.194.34.110
Name:   google.com
Address: 173.194.34.105

 

and telnet ok

 

[oracle@test2 ~]$ telnet google.com 80
Trying 173.194.34.103...
Connected to google.com (173.194.34.103).
Escape character is '^]'.
GET / HTTP/1.0
HTTP/1.0 302 Found
Location: http://www.google.co.uk/?gws_rd=cr&ei=aKlKUvKDH8K80QXM1oGABg
Cache-Control: private
Content-Type: text/html; charset=UTF-8
Set-Cookie: PREF=ID=636a9a715d3e713a:FF=0:TM=1380624744:LM=1380624744:S=UmAm64le9UZRtDQE; expires=Thu, 01-Oct-2015 10:52:24 GMT; path=/; domain=.google.com
Set-Cookie: NID=67=vydxBJQUOyjK20AY5G_h7yd23MWHY9L1dxCNRTnkwaVilDsEdMViDB9bbkecMILO7U9SBpTQqGpwBR9y0pL1qcdj0Mx_Rdh_Gu0D3KiunmSIV1nrRdV4Q3T3Y4MKDFLz; expires=Wed, 02-Apr-2014 10:52:24 GMT; path=/; domain=.google.com; HttpOnly
P3P: CP="This is not a P3P policy! See http://www.google.com/support/accounts/bin/answer.py?hl=en&answer=151657 for more info."
Date: Tue, 01 Oct 2013 10:52:24 GMT
Server: gws
Content-Length: 261
X-XSS-Protection: 1; mode=block
X-Frame-Options: SAMEORIGIN
Alternate-Protocol: 80:quic
<HTML><HEAD><meta http-equiv="content-type" content="text/html;charset=utf-8">
<TITLE>302 Moved</TITLE></HEAD><BODY>
<H1>302 Moved</H1>
The document has moved
<A HREF="http://www.google.co.uk/?gws_rd=cr&ei=aKlKUvKDH8K80QXM1oGABg">here</A>.
</BODY></HTML>
Connection closed by foreign host.
[oracle@test2 ~]$

 

So ping/nslookup/telnet from DB o/s user is ok - UTL_TCP (using TcpPortPing function) is ok - but UTL_HTTP returns ACL errors ?

 

Help please ?!

 

ps - If i run utl_http as SYS user it also fails

 

SQL> Session [1] SYS@ORACLE_TEST                    
SQL> select utl_http.request('google.com') from dual    
                                                        
   1 select utl_http.request('google.com') from dual    
            *                                           
ORA-29273: HTTP request failed                          
ORA-06512: at "SYS.UTL_HTTP", line 1722                 
ORA-12543: TNS:destination host unreachable             
ORA-06512: at line 1                                    
  • 1. Re: Problems with UTL_HTTP and network access problems
    Anar Godjaev Expert
    Currently Being Moderated

    Hi, Please check step bye step

     

    BEGIN

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

    acl => 'http_service.xml',

    description => 'HTTP ACL',

    principal => 'schemas_name',

    is_grant => true,

    privilege => 'connect');

     

     

    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

    acl => 'http_service.xml',

    principal => 'schemas_name',

    is_grant => true,

    privilege => 'resolve');

     

     

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

    acl => 'http_service.xml',

    host => 'ip_name');

     

    COMMIT;

    END;

    /

  • 2. Re: Problems with UTL_HTTP and network access problems
    Brent Harlow Newbie
    Currently Being Moderated
    SQL> Session [1] SYS@ORCL_PULSE_TEST                        
    SQL> BEGIN                                                  
       2                                                        
       3 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(                     
       4 acl => 'http_service.xml',                             
       5 description => 'HTTP ACL',                             
       6 principal => 'TEST_ACL_USER',                          
       7 is_grant => true,                                      
       8 privilege => 'connect');                               
       9                                                        
      10 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(                  
      11 acl => 'http_service.xml',                             
      12 principal => 'TEST_ACL_USER',                          
      13 is_grant => true,                                      
      14 privilege => 'resolve');                               
      15                                                        
      16                                                        
      17 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(                     
      18 acl => 'http_service.xml',                             
      19 host => 'www.google.com');                             
      20                                                        
      21 COMMIT;                                                
      22                                                        
      23 END;                                                   
                                                                
    PL/SQL procedure successfully completed      

     

    Oracle Database 11g Release 11.2.0.1.0 - Production             
    PL/SQL Release 11.2.0.1.0 - Production                          
    CORE 11.2.0.1.0 Production                                      
    TNS for Linux: Version 11.2.0.1.0 - Production                  
    NLSRTL Version 11.2.0.1.0 - Production                          
                                                                    
    SQL> Session [1] TEST_ACL_USER@ORCL_PULSE_TEST                  
    SQL> select tcpportping('www.google.com',80) from dual          
                                                                    
    TCPPORTPING('WWW.GOOGLE.COM',80)                                
    --------------------------------                                
                                0.01                                
                                                                    
    1 row selected                                                  
                                                                    
    Elapsed time: 00:00:00.506                                      
    SQL> select utl_http.request('www.google.com') from dual        
                                                                    
       1 select utl_http.request('www.google.com') from dual        
                *                                                   
    ORA-29273: HTTP request failed                                  
    ORA-06512: at "SYS.UTL_HTTP", line 1722                         
    ORA-24247: network access denied by access control list (ACL)   
    ORA-06512: at line 1                                            

     

    Same result ?

  • 3. Re: Problems with UTL_HTTP and network access problems
    Anar Godjaev Expert
    Currently Being Moderated

    Hi

     

    Please send me result

     

    1) select * from dba_network_acls;


    2)   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;


    and please give grant to TEST_ACL_USER schams.

    grant execute on utl_http to TEST_ACL_USER


    Thank you

  • 4. Re: Problems with UTL_HTTP and network access problems
    Brent Harlow Newbie
    Currently Being Moderated

    SQL> select * from dba_network_acls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

       2 where acl like '%http_service.xml'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    HOST                                                                                                                                                                                                                                                            LOWER_PORT UPPER_PORT ACL                                                                                                                                                                                                                                                             ACLID                           

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------

    www.google.com                                                                                                                                                                                                                                                                        /sys/acls/http_service.xml                                                                                                                                                                                                                                      E7AC059ECC1464FEE040000A1E003FB2

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    1 row selected                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    Elapsed time: 00:00:01.304                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

    SQL> SELECT acl,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

       2     principal,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

       3     privilege,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

       4     is_grant                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

       5     FROM dba_network_acl_privileges                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

       6     where principal = 'TEST_ACL_USER'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    ACL                                                                                                                                                                                                                                                             PRINCIPAL                                                                                                                                                                                                                                                       PRIVILE IS_GRANT                                      

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- --------------------                          

    /sys/acls/http_service.xml                                                                                                                                                                                                                                      TEST_ACL_USER                                                                                                                                                                                                                                                   connect true                                          

    /sys/acls/http_service.xml                                                                                                                                                                                                                                      TEST_ACL_USER                                                                                                                                                                                                                                                   resolve true                                          

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    2 rows selected                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

    Elapsed time: 00:00:00.975       

     

    Oracle Database 11g Release 11.2.0.1.0 - Production                                                                                                                         

    PL/SQL Release 11.2.0.1.0 - Production                                                                                                                                      

    CORE 11.2.0.1.0 Production                                                                                                                                                  

    TNS for Linux: Version 11.2.0.1.0 - Production                                                                                                                              

    NLSRTL Version 11.2.0.1.0 - Production                                                                                                                                      

                                                                                                                                                                                

    SQL> Session [1] TEST_ACL_USER@ORCL_PULSE_TEST                                                                                                                              

    SQL> select * from user_tab_privs                                                                                                                                           

                                                                                                                                                                                

    GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE

    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---

    TEST_ACL_USER                  SYS                            UTL_HTTP                       SYS                            EXECUTE                                  NO  NO 

                                                                                                                                                                                

    1 row selected                                                                                                                                                              

                                                                                                                                                                                

    Elapsed time: 00:00:00.426                                                                                                                                                  

    SQL> select utl_http.request('www.google.com') from dual                                                                                                                    

                                                                                                                                                                                

       1 select utl_http.request('www.google.com') from dual                                                                                                                    

                *                                                                                                                                                               

    ORA-29273: HTTP request failed                                                                                                                                              

    ORA-06512: at "SYS.UTL_HTTP", line 1722                                                                                                                                     

    ORA-24247: network access denied by access control list (ACL)                                                                                                               

    ORA-06512: at line 1                                                                                                                                                        

  • 5. Re: Problems with UTL_HTTP and network access problems
    Anar Godjaev Expert
    Currently Being Moderated

    You can check it out I

     

    select utl_http.request('http://www.google.com') from dual;

  • 8. Re: Problems with UTL_HTTP and network access problems
    Brent Harlow Newbie
    Currently Being Moderated

    makes no difference if I specify http or not

     

    SQL> Session [1] TEST_ACL_USER@ORCL_PULSE_TEST                     
    SQL> select utl_http.request('http://www.google.com') from dual     
                                                                       
       1 select utl_http.request('http://www.google.com') from dual     
                *                                                      
    ORA-29273: HTTP request failed                                     
    ORA-06512: at "SYS.UTL_HTTP", line 1722                            
    ORA-24247: network access denied by access control list (ACL)      
    ORA-06512: at line 1                        

     

    I am very familar with the Oracle-Base article on ACL - been through it carefully many time trying to get this to work !  I have exactly the same setup on my local laptop and it works fine.

     

    I don't understand why UTL_TCP can connect while UTL_HTTP can't and how to diagnose the network isuse (if that's what it is)

     

    The fact that running UTL_HTTP as the SYS user also gives an error suggests that the problem is beyond a simple ACL issue ?

     

    Oracle Database 11g Release 11.2.0.1.0 - Production                
    PL/SQL Release 11.2.0.1.0 - Production                             
    CORE 11.2.0.1.0 Production                                         
    TNS for Linux: Version 11.2.0.1.0 - Production                     
    NLSRTL Version 11.2.0.1.0 - Production                             
                                                                       
    SQL> Session [1] SYS@ORCL_PULSE_TEST                               
    SQL> select utl_http.request('http://www.google.com') from dual    
                                                                       
       1 select utl_http.request('http://www.google.com') from dual    
                *                                                      
    ORA-29273: HTTP request failed                                     
    ORA-06512: at "SYS.UTL_HTTP", line 1722                            
    ORA-12543: TNS:destination host unreachable                        
    ORA-06512: at line 1                
  • 9. Re: Problems with UTL_HTTP and network access problems
    Anar Godjaev Expert
    Currently Being Moderated

    Could you please repeat all this process over SYS ? Can we control this for test like that ?

     

    Note:  I did the test. My test was working

     

    SQL> select utl_http.request('http://www.google.com') from dual;

     

     

    UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')

    --------------------------------------------------------------------------------

    <!doctype html><html itemscope="" itemtype="http://schema.org/WebPage"><head><me

    ta itemprop="image" content="/images/google_favicon_128.png"><title>Google</titl

    e><script>(function(){

    window.google={kEI:"osJKUpqjJoHLtQbHloCIBQ",getEI:function(a){for(var b;a&&(!a.g

    etAttribute||!(b=a.getAttribute("eid")));)a=a.parentNode;return b||google.kEI},h

    ttps:function(){return"https:"==window.location.protocol},kEXPI:"17259,4000116,4

    004949,4006293,4006727,4007229,4007278,4007661,4007714,4008067,4008133,4008142,4

    008488,4008698,4008725,4008750,4009002,4009033,4009037,4009220,4009386,4009565,4

    009566,4009663,4009671,4009811,4009813,4009873,4009882,4010057,4010061,4010079,4

    010131,4010178,4010184,4010271,4010555,4010558,4010578,4010665,4010685,4010853,4

    010858,4010899,4010919,4010938,4011021,4011030,4011060,4011067,4011112,4011125,4

     

     

    UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')

    --------------------------------------------------------------------------------

    011227,4011228,4011243,4011245,4011256,4011278,4011301,4011305,4011423,4011469",

    kCSI:{e:"17259,4000116,4004949,4006293,4006727,4007229,4007278,4007661,4007714,4

    008067,4008133,4008142,4008488,4008698,4008725,4008750,4009002,4009033,4009037,4

    009220,4009386,4009565,4009566,4009663,4009671,4009811,4009813,4009873,4009882,4

    010057,4010061,4010079,4010131,4010178,4010184,4010271,4010555,4010558,4010578,4

    010665,4010685,4010853,4010858,4010899,4010919,4010938,4011021,4011030,4011060,4

    011067,4011112,4011125,4011227,4011228,4011243,4011245,4011256,4011278,4011301,4

    011305,4011423,4011469",ei:"osJKUpqjJoHLtQbHloCIBQ"},authuser:0,ml:function(){},

    kHL:"az",time:function(){return(new Date).getTime()},log:function(a,b,c,l,k){var

    d=new Image,f=google.lc,e=google.li,g="",h="gen_204";k&&(h=

    k);d.onerror=d.onload=d.onabort=function(){delete f[e]};f[e]=d;c||-1!=b.search("

     

     

    UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')

    --------------------------------------------------------------------------------

    &ei=")||(g="&ei="+google.getEI(l));c=c||"/"+h+"?atyp=i&ct="+a+"&cad="+b+g+"&zx="

    +google.time();

    a=/^http:/i;a.test(c)&&google.https()?(google.ml(Error("GLMM"),!1,{src:c}),delet

    e f[e]):(d.src=c,google.li=e+1)},lc:[],li:0,Toolbelt:{},y:{},x:function(a,

    b){google.y[a.id]=[a,b];return!1},load:function(a,b,c){google.

  • 10. Re: Problems with UTL_HTTP and network access problems
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    BrentHarlow wrote:

     

    I don't understand why UTL_TCP can connect while UTL_HTTP can't and how to diagnose the network isuse (if that's what it is)

    UTL_HTTP effectively uses UTL_TCP as it is the same TCP interface and modules used in the database kernel.

     

    Ficken editor screws up code posted, and I'm tired of fighting it - so a plain copy-and-paste of code without formatting:

     

    SQL> create or replace function HttpGet( ipAddress varchar2, portNum number default 80, url varchar2 default null ) return TStrings pipelined is
      2      socket  utl_tcp.connection;
      3      cmd varchar2(1024);
      4      sz  integer;
      5      linevarchar2(4000);

      6  begin

      7      socket := utl_tcp.Open_Connection(
      8                      remote_host => ipAddress,
      9                      remote_port => portNum,
    10                      tx_timeout  => 5
    11              );
    12      pipe row( 'Connected to '||ipAddress||':'||portNum );

    13 

    14      cmd := 'GET '||nvl(url,'/')||' HTTP/1.0' || utl_tcp.CRLF;
    15      pipe row( cmd );
    16      sz := utl_tcp.Write_Text( socket, cmd, length(cmd) );

    17 

    18      cmd := utl_tcp.CRLF;
    19      sz := utl_tcp.Write_Text( socket, cmd, length(cmd) );

    20 

    21      begin
    22              loop
    23                      pipe row( utl_tcp.get_line(socket) );
    24              end loop;
    25      exception when utl_tcp.end_of_input then
    26              null;
    27      end;

    28 

    29      utl_tcp.Close_Connection( socket );
    30      pipe row( 'Connection closed' );

    31 

    32 

    33  end;

    34  /

     

    Function created.

     

    SQL>

  • 11. Re: Problems with UTL_HTTP and network access problems
    Brent Harlow Newbie
    Currently Being Moderated

    Hi Billy,

     

    I've created the function you provided and run ...

     

    SQL> select tcpportping('www.google.com',80) from dual                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                    
    TCPPORTPING('WWW.GOOGLE.COM',80)                                                                                                                                                                                                                                
    --------------------------------                                                                                                                                                                                                                                
                                   0                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                    
    1 row selected                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                    
    Elapsed time: 00:00:00.412                                                                                                                                                                                                                                      
    SQL> select * from table (HttpGet('www.google.com'))                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                    
    COLUMN_VALUE                                                                                                                                                                                                                                                    
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
    Connected to www.google.com:80                                                                                                                                                                                                                                  
    GET / HTTP/1.0
                                                                                                                                                                                                                                                    
    HTTP/1.0 302 Found
                                                                                                                                                                                                                                                
    Location: http://www.google.co.uk/?gws_rd=cr&ei=uflKUvWUIqik0AW384GADw
                                                                                                                                                                                            
    Cache-Control: private
                                                                                                                                                                                                                                            
    Content-Type: text/html; charset=UTF-8
                                                                                                                                                                                                                            
    Set-Cookie: PREF=ID=a8fc96efb3c3909a:FF=0:TM=1380645305:LM=1380645305:S=QfyDAbMV7YhPQp5B; expires=Thu, 01-Oct-2015 16:35:05 GMT; path=/; domain=.google.com
                                                                                                       
    Set-Cookie: NID=67=uYvGBk-ZclfwRy7zNffCtAcZfFdRB_QF40quUrmbGbgC729rqjmiXbCQoe6kuFCHQ2oXpGJ2nyyYvmLc6GtoTfFwklMvhzx9IRankZAZHJjURZKvfNjIg29W1-sOu36M; expires=Wed, 02-Apr-2014 16:35:05 GMT; path=/; domain=.google.com; HttpOnly
                                  
    P3P: CP="This is not a P3P policy! See http://www.google.com/support/accounts/bin/answer.py?hl=en&answer=151657 for more info."
                                                                                                                                   
    Date: Tue, 01 Oct 2013 16:35:05 GMT
                                                                                                                                                                                                                               
    Server: gws
                                                                                                                                                                                                                                                       
    Content-Length: 261
                                                                                                                                                                                                                                               
    X-XSS-Protection: 1; mode=block
                                                                                                                                                                                                                                   
    X-Frame-Options: SAMEORIGIN
                                                                                                                                                                                                                                       
    Alternate-Protocol: 80:quic
                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                  
    <HTML><HEAD><meta http-equiv="content-type" content="text/html;charset=utf-8">
                                                                                                                                                                                     
    <TITLE>302 Moved</TITLE></HEAD><BODY>
                                                                                                                                                                                                                              
    <H1>302 Moved</H1>
                                                                                                                                                                                                                                                 
    The document has moved
                                                                                                                                                                                                                                             
    <A HREF="http://www.google.co.uk/?gws_rd=cr&ei=uflKUvWUIqik0AW384GADw">here</A>.
                                                                                                                                                                              
    </BODY></HTML>
                                                                                                                                                                                                                                                    
    Connection closed                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                    
    23 rows selected                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                    
    Elapsed time: 00:00:00.621                                                                                                                                                                                                                                      
    SQL> select utl_http.request('www.google.com') from dual                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                    
       1 select utl_http.request('www.google.com') from dual                                                                                                                                                                                                        
                *                                                                                                                                                                                                                                                   
    ORA-29273: HTTP request failed                                                                                                                                                                                                                                  
    ORA-06512: at "SYS.UTL_HTTP", line 1722                                                                                                                                                                                                                         
    ORA-24247: network access denied by access control list (ACL)                                                                                                                                                                                                   
    ORA-06512: at line 1          

     

     

    so httpget works fine but not utl_http.request ?  Why ?

     

    Cheers,

    Brent

  • 12. Re: Problems with UTL_HTTP and network access problems
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Because of the redirect in the HTML.

     

    The above GET is incomplete - as it is not the actual page. It redirects using a HTTP 302 code and provides the URL http://www.google.co.uk/?gws_rd=cr&ei=uflKUvWUIqik0AW384GADw to redirect to.


    The HttpGet is stupid and does not recognise the "document/page has moved here" response. The HTTP code in Oracle does. And moves along as these are not the droids page, it was looking for.


    I would expect this to fail the ACL. Try this server using the HttpGet test function, e.g.

    select * from table (HttpGet('www.google.co.uk'))

  • 13. Re: Problems with UTL_HTTP and network access problems
    Brent Harlow Newbie
    Currently Being Moderated
    SQL> select * from table (HttpGet('www.google.co.uk'))    
                                                                                                                                                                                                                                                                    
       1 select * from table (HttpGet('www.google.co.uk'))                                                                                                                                                                                                          
                              *                                                                                                                                                                                                                                     
    ORA-24247: network access denied by access control list (ACL)                                                                                                                                                                                                   
    ORA-06512: at "SYS.UTL_TCP", line 17                                                                                                                                                                                                                            
    ORA-06512: at "SYS.UTL_TCP", line 246                                                                                                                                                                                                                           
    ORA-06512: at "TEST_ACL_USER.HTTPGET", line 7                                                                                                                                                                                                                   
    ORA-06512: at line 1                                    
                                                                                                                                                                                                           
    SQL> select * from table (HttpGet('www.google.com'))                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                    
    COLUMN_VALUE                                                                                                                                                                                                                                                    
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
    Connected to www.google.com:80                                                                                                                                                                                                                                  
    GET / HTTP/1.0
                                                                                                                                                                                                                                                    
    HTTP/1.0 302 Found
                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                       
    SQL> select utl_http.request('www.google.com') from dual                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                    
       1 select utl_http.request('www.google.com') from dual                                                                                                                                                                                                        
                *                                                                                                                                                                                                                                                   
    ORA-29273: HTTP request failed                                                                                                                                                                                                                                  
    ORA-06512: at "SYS.UTL_HTTP", line 1722                                                                                                                                                                                                                         
    ORA-24247: network access denied by access control list (ACL)                                                                                                                                                                                                   
    ORA-06512: at line 1                                                                                                                                                                                                                                            
    SQL> select utl_http.request('www.google.co.uk') from dual                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                    
       1 select utl_http.request('www.google.co.uk') from dual                                                                                                                                                                                                      
                *                                                                                                                                                                                                                                                   
    ORA-29273: HTTP request failed                                                                                                                                                                                                                                  
    ORA-06512: at "SYS.UTL_HTTP", line 1722                                                                                                                                                                                                                         
    ORA-24247: network access denied by access control list (ACL)                                                                                                                                                                                                   
    ORA-06512: at line 1                                   

     

    Correct :

     

    HttpGet('www.google.co.uk') returns ACL error

    HttpGet('www.google.com') returns successfully

     

    but BOTH of these fail with exactly the same error message

     

    select utl_http.request('www.google.com') from dualv

    select utl_http.request('www.google.co.uk') from dual

     

    Any idea where to go from here ? I'm stuck !!

     

    Cheers,

    Brent

  • 14. Re: Problems with UTL_HTTP and network access problems
    thomaso Journeyer
    Currently Being Moderated

    If I understand Billy's explanation correctly, all you need to do is to add a new url to your ACL list:

     

    begin

       DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'http_service.xml', host => ''www.google.co.uk'');

    end;

     

    T.

     

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points