1 2 Previous Next 22 Replies Latest reply: Oct 4, 2013 12:02 AM by Billy~Verreynne RSS

    Problems with UTL_HTTP and network access problems

    Brent Harlow

      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

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

              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

                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

                  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

                    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

                      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
                        Billy~Verreynne

                        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

                          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
                            Billy~Verreynne

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

                                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