1 2 Previous Next 22 Replies Latest reply: Oct 4, 2013 12:02 AM by Billy~Verreynne Go to original post RSS
      • 15. Re: Problems with UTL_HTTP and network access problems
        Brent Harlow

        Hi Thomaso,

         

        No, I don't want to connect to "google.co.uk". In my example I need to be able to have UTL_HTTP.REQUEST work for google.com - which is a url in the ACL list - which is why the tcp test works ok.  The tcp tests are fine but I cannot get the UTL_HTTP.REQUEST to work - even with the url in the ACL list. The exact same setup worked fine first time on my laptop but not on our test servers. I'm convinced it's something to do with our network/switching but I have no idea how to diagnose this. Clearly a connection is going out ok, it's just the response that's not getting back to the DB ??

         

        Cheers,

        Brent

        • 16. Re: Problems with UTL_HTTP and network access problems
          Billy~Verreynne

          Does not matter what you want in this case Brent.

           

          google.com is refusing to service you, is sending a HTTP 302 in response, and redirecting you to google.co.uk.

           

          Unless you have an IP address that google deems need to be serviced by google.com, it will direct you to the closest google data centre for your IP - which happens to the google.co.uk centre in your case.

           

          This is not a TCP issue. It is a HTTP application protocol issue that allows a web server to refuse to service you a specific page, and tell you to find that page on another server. See  Redirection - SEO Best Practices for a discussion on this, how it is typically used, and how to implement it on Apache.

          • 17. Re: Problems with UTL_HTTP and network access problems
            Brent Harlow

            Hi Billy,

             

            Ok, perhaps I have confused the issue by using google as a test case.  The real URL I am trying to connect to is "api.esendex.com". I was just trying to provide a simplier example of how I am able to receive a successful response back on my local laptop but not from our test servers. The actual code to connect to api.esendex.com is :

             

            CREATE OR REPLACE PROCEDURE esendex_retrieve as

             

              ESENDEX_USERNAME  constant  varchar2(100) := 'brent@hmdclinical.com';

              ESENDEX_PASSWORD  constant  varchar2(50) := 'XXXXXXXXXXXX';

              ESENDEX_CHECK_MSG_URL constant varchar2(100) := 'https://api.esendex.com/v1.0/inbox/messages';

              request   utl_http.req;

              response  utl_http.resp;

              response_code  varchar2(10);

              line     clob; -- response line

              content  clob; -- response content

              eof     boolean;

             

            begin

             

              utl_http.set_wallet('file:/u01/app/oracle/admin/orcl/wallet','xxxxxxxxxx');

              request := utl_http.begin_request(ESENDEX_CHECK_MSG_URL);

              utl_http.set_authentication(request, ESENDEX_USERNAME, ESENDEX_PASSWORD);

             

              -- read response file

              response := utl_http.get_response(request);

              response_code := response.status_code;


            exception

            when others then

               utl_http.end_response(response);

            end;

            /

             

            But if I add google.co.uk as  URL to my ACL on both my laptop and our test servers:

             

            LOCAL :


            Oracle Database 11g Release 11.2.0.1.0 - 64bit Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

            PL/SQL Release 11.2.0.1.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

            CORE 11.2.0.1.0 Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

            TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

            NLSRTL Version 11.2.0.1.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            SQL> Session [1] SYS@ORCL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

            SQL> select * from dba_network_acls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            HOST                                                                                                                                                                                                                                                            LOWER_PORT UPPER_PORT ACL                                                                                                                                                                                                                                                             ACLID                           

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

            www.google.co.uk                                                                                                                                                                                                                                                                      /sys/acls/esendex_acl_file.xml                                                                                                                                                                                                                                  DA3AFDBA93124EA18493CF86E5B05E55

            api.esendex.com                                                                                                                                                                                                                                                                       /sys/acls/esendex_acl_file.xml                                                                                                                                                                                                                                  DA3AFDBA93124EA18493CF86E5B05E55

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            2 rows selected                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            Elapsed time: 00:00:00.961                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

            SQL> SELECT acl,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

               2        principal,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

               3        privilege,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

               4        is_grant                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

               5        FROM dba_network_acl_privileges                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            ACL                                                                                                                                                                                                                                                             PRINCIPAL                                                                                                                                                                                                                                                       PRIVILE IS_GRANT                                      

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

            /sys/acls/esendex_acl_file.xml                                                                                                                                                                                                                                  TEST_ACL_USER                                                                                                                                                                                                                                                  connect true                                          

            /sys/acls/esendex_acl_file.xml                                                                                                                                                                                                                                  TEST_ACL_USER                                                                                                                                                                                                                                                  resolve true                                          

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            2 rows selected                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            Elapsed time: 00:00:00.715  

             

            The three tests on LOCAL (all successful)

             

            SQL> select tcpportping('www.google.co.uk',80) from dual                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                           

            TCPPORTPING('WWW.GOOGLE.CO.UK',80)                                                                                                                                                                                                                             

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

                                          0.03                                                                                                                                                                                                                             

                                                                                                                                                                                                                                                                           

            1 row selected                                                                                                                                                                                                                                                 

                                                                                                                                                                                                                                                                           

            Elapsed time: 00:00:00.290                                                                                                                                                                                                                                     

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

                                                                                                                                                                                                                                                                           

            COLUMN_VALUE                                                                                                                                                                                                                                                   

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

            Connected to www.google.co.uk:80                                                                                                                                                                                                                               

            GET / HTTP/1.0

                                                                                                                                                                                                                                                           

            HTTP/1.0 302 Found

                                                                                                                                                                                                                                                       

            Location: http://www.google.co.uk/?gws_rd=cr&ei=EDBNUoSjI-fJ0QW7iYCYAw

                                                                                                                                                                                                   

            Cache-Control: private

                                                                                                                                                                                                                                                   

            Content-Type: text/html; charset=UTF-8

                                                                                                                                                                                                                                   

            Set-Cookie: PREF=ID=0ee6f35331ac77fb:FF=0:TM=1380790288:LM=1380790288:S=FwOKfqoIb-AE7OTf; expires=Sat, 03-Oct-2015 08:51:28 GMT; path=/; domain=.google.com

                                                                                                              

            Set-Cookie: NID=67=elDplKl_UqsfFYKQOR_6Z7OhMmO5Gcw48-hhynNZIqbb5J-0UfzOSPylFVIFrQu80JBf3nfXzmVjBsFdXetphBk8odxn8VfebpsLA1IZSfcXkgr-csfnfpGBmmxKsnl0; expires=Fri, 04-Apr-2014 08:51:28 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: Thu, 03 Oct 2013 08:51:28 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=EDBNUoSjI-fJ0QW7iYCYAw">here</A>.

                                                                                                                                                                                     

            </BODY></HTML>

                                                                                                                                                                                                                                                           

            Connection closed                                                                                                                                                                                                                                              

                                                                                                                                                                                                                                                                           

            23 rows selected                                                                                                                                                                                                                                               

                                                                                                                                                                                                                                                                           

            Elapsed time: 00:00:00.321                                                                                                                                                                                                                                     

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

                                                                                                                                                                                                                                                                           

            UTL_HTTP.REQUEST('WWW.GOOGLE.CO.UK')                                                                                                                                                                                                                           

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

            <!doctype html><html itemscope="" itemtype="http://schema.org/WebPage"><head><meta itemprop="image" content="/images/google_favicon_128.png"><title>Google</title><script>(function(){

            window.google={kEI:"FTBNUsf6A_C20QWbiIHIAw",getEI:function(a){for(var b;

                                                                                                                                                                                                                                                                           

            1 row selected                                                                                                                                                                                                                                                 

                                                                                                                                                                                                                                                                           

            Elapsed time: 00:00:00.376     

             

            and on TEST SERVER                                                                                                                                                                                                                              

             

            SQL> select * from dba_network_acls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

               2 where acl='/sys/acls/http_service.xml'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

            HOST                                                                                                                                                                                                                                                            LOWER_PORT UPPER_PORT ACL                                                                                                                                                                                                                                                             ACLID                           

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

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

            api.esendex.com                                                                                                                                                                                                                                                                       /sys/acls/http_service.xml                                                                                                                                                                                                                                      E7AC059ECC1464FEE040000A1E003FB2

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

            www.google.co.uk                                                                                                                                                                                                                                                                      /sys/acls/http_service.xml                                                                                                                                                                                                                                      E7AC059ECC1464FEE040000A1E003FB2

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            4 rows selected                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

            Elapsed time: 00:00:01.208                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

            SQL> SELECT acl,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

               2        principal,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

               3        privilege,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

               4        is_grant                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

               5        FROM dba_network_acl_privileges                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

               6        where acl='/sys/acls/http_service.xml'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

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

            The same three tests on TEST (UTL_HTTP fails)


            SQL> select tcpportping('www.google.co.uk',80) from dual                                                                                                                                                                                                       

                                                                                                                                                                                                                                                                           

            TCPPORTPING('WWW.GOOGLE.CO.UK',80)                                                                                                                                                                                                                             

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

                                             0                                                                                                                                                                                                                             

                                                                                                                                                                                                                                                                           

            1 row selected                                                                                                                                                                                                                                                 

                                                                                                                                                                                                                                                                           

            Elapsed time: 00:00:00.349                                                                                                                                                                                                                                     

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

                                                                                                                                                                                                                                                                           

            COLUMN_VALUE                                                                                                                                                                                                                                                   

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

            Connected to www.google.co.uk:80                                                                                                                                                                                                                               

            GET / HTTP/1.0

                                                                                                                                                                                                                                                           

            HTTP/1.0 302 Found

                                                                                                                                                                                                                                                       

            Location: http://www.google.co.uk/?gws_rd=cr&ei=lzFNUq2QI4bt0gXG4oDAAg

                                                                                                                                                                                                   

            Cache-Control: private

                                                                                                                                                                                                                                                   

            Content-Type: text/html; charset=UTF-8

                                                                                                                                                                                                                                   

            Set-Cookie: PREF=ID=537a54885635bf35:FF=0:TM=1380790679:LM=1380790679:S=J4r7589i3cyg1GRy; expires=Sat, 03-Oct-2015 08:57:59 GMT; path=/; domain=.google.com

                                                                                                              

            Set-Cookie: NID=67=LFnRXrlCnr07yuTS3HGqE4eOH0h4W1dIhRr4IBANQD7qeObqe8h2BZ6BtvhC3ARYn2huOFINGlDoY25PzIxIEg5fk9M2VjpixKhiTyCi04H-t-fVkAxIjaqx1b9pWAU7; expires=Fri, 04-Apr-2014 08:57:59 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: Thu, 03 Oct 2013 08:57:59 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=lzFNUq2QI4bt0gXG4oDAAg">here</A>.

                                                                                                                                                                                     

            </BODY></HTML>

                                                                                                                                                                                                                                                           

            Connection closed                                                                                                                                                                                                                                              

                                                                                                                                                                                                                                                                           

            23 rows selected                                                                                                                                                                                                                                               

                                                                                                                                                                                                                                                                           

            Elapsed time: 00:00:00.703                                                                                                                                                                                                                                     

            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                           

             

            Do you mean that www.google.co.uk is refusing the connection from our test server while allowing it from my laptop ?

            • 18. Re: Problems with UTL_HTTP and network access problems
              Brent Harlow

              Hey guys,

               

              Problem solved !  Thanks so much to Billy, thomaso and Anar for helping.

               

              Turns out the problem was an incorrectly set linux http_proxy variable set !!  I was able to pinpoint that by using curl on the linux command line and sending the trace out to a file  "curl --trace-ascii debugdump.txt http://www.google.co.uk". The trace file showed the incorrect use of the http_proxy variable. Once I unset the variable and restarted the database - all was fine. Just a shame there was not better logging from oracle - error message was really a red herring.

               

              ** Embarrassed ** Sorry guys !

               

              Cheers,

              Brent

              • 19. Re: Problems with UTL_HTTP and network access problems
                Anar Godjaev

                HI BrentHarlow, it is very good. Good to know  the problem has been solved.  

                • 20. Re: Problems with UTL_HTTP and network access problems
                  Billy~Verreynne

                  How would Linux o/s proxy variables influence code in Oracle - unless it honour the environment variable settings?

                   

                  I have always used the proxy settings provided by UTL_HTTP itself. (works exactly same way/format as http_proxy in Linux). Also makes the code a bit more robust IMO as it removes the need for external moving parts for http proxy use inside Oracle.

                   

                  But glad to hear you got the problem licked.

                  • 21. Re: Problems with UTL_HTTP and network access problems
                    Brent Harlow

                    Clearly Oracle does use the o/s proxy variables. In fact, just unsetting the variables was not enough to make the UTL_HTTP work. Only after I had restarted the database did it start working, so Oracle must check and maintain o/s proxy from startup.

                     

                    Thanks Billy !

                    • 22. Re: Problems with UTL_HTTP and network access problems
                      Billy~Verreynne

                      The Oracle o/s processes inherits their environment settings from the o/s process starting them.

                       

                      To change settings can be done by shutting down the listener, applying a new environment and starting the listener from that. As it spawns Oracle o/s server processes for dedicated server sessions, these sessions will inherit the environment from the listener. However, PQ processes, job queue processes, and shared server processes (all also servicing clients) will remain unchanged. Thus the need for a shutdown and restart with updated environment settings.

                       

                      The reason I find the http_proxy variable thing unexpected, is that in my view it is a security violation to set it for clients in a server system (like Oracle) to use. Instead, the end-user or application (like PL/SQL) need to set it and supply proxy credentials. And it also has unintended impact on PL/SQL code as you've discovered.

                      1 2 Previous Next