11 Replies Latest reply: Jun 3, 2009 5:01 AM by 536256 RSS

    Error using UTL_HTTP over HTTPS


      I am trying to simply connect to an HTTPS web site. I used this query to test the connection :

      select utl_http.request('https://subdomain.maindomain.com/webservice.wsdl',null, 'file:C:\MyWalletDirectory','WalletPassword') from dual;

      and I always got the following error :
      ORA-29273: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1577
      ORA-29268: HTTP client error
      ORA-06512: at line 1

      Can anybody help me with this issue ? Is it possible to have a more meaningfull message ?


        • 1. Re: Error using UTL_HTTP over HTTPS
          If you see the documentation

          ORA-29273: HTTP request failed
          Cause: The UTL_HTTP package failed to execute the HTTP request.
          Action: Use get_detailed_sqlerrm to check the detailed error message. Fix the error and retry the HTTP request.

          Hope it´s useful
          • 2. Re: Error using UTL_HTTP over HTTPS
            I've written a basic PL/SQL web browser template. It looks/works as follows:

            SQL> select * from TABLE( webbrowser('https://metalink.oracle.com') ) where rownum < 11;
            Setting browser configuration
            Wallet set to file:/etc/ORACLE/WALLETS/oracle/
            Proxy Server is
            Proxy URL modified to include proxy user name and password
            Proxy URL is http://verreyb:*****@
            HTTP: GET https://metalink.oracle.com
            <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
            <HTML><HEAD><TITLE>Welcome to Oracle MetaLink</TITLE>
            <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"><LINK
            href="oracle.htm" rel=stylesheet>
            10 rows selected.
            The source code:
            create or replace type TStrings is table of varchar2(4000);
            create or replace function WebBrowser( url varchar2 ) return TStrings pipelined is
            -- supports http, https and proxy servers
                    -- fixed constants
                    C_NO_PROXY_FOR  constant varchar2(4000) := 'localhost';
                    C_WALLET        constant varchar2(4000) := 'file:/etc/ORACLE/WALLETS/oracle/';
                    C_WALLET_PASS   constant varchar2(4000) := '<wallet password goes here>';
                    -- Proxy settings that can be made arguments in the WebBrowser() call
                    proxyServer     varchar2(20) := '';
                    -- not all proxy servers use authentication, but many corporate proxies do, in
                    -- which case you need to specify your auth details here
                    -- (make it nulls if not applicable)
                    proxyUser       varchar2(50) := '<proxy username goes here>';
                    proxyPass       varchar2(50) := '<proxy password goes here>';
                    -- our local variables
                    proxyURL        varchar2(4000);
                    request         UTL_HTTP.req;
                    response        UTL_HTTP.resp;
                    buffer          varchar2(4000);
                    endLoop         boolean;
                    -- our "browser" settings
                    PIPE ROW( 'Setting browser configuration' );
                    UTL_HTTP.set_response_error_check( TRUE );
                    UTL_HTTP.set_detailed_excp_support( TRUE );
                    UTL_HTTP.set_cookie_support( TRUE );
                    UTL_HTTP.set_transfer_timeout( 30 );
                    UTL_HTTP.set_follow_redirect( 3 );
                    UTL_HTTP.set_persistent_conn_support( TRUE );
                    -- set wallet for HTTPS access
                    PIPE ROW( 'Wallet set to '||C_WALLET );
                    UTL_HTTP.set_wallet( C_WALLET, C_WALLET_PASS );
                    -- configure for proxy access
                    if proxyServer is not NULL then
                            PIPE ROW( 'Proxy Server is '||proxyServer );
                            proxyURL := 'http://'||proxyServer;
                            if (proxyUser is not NULL) and (proxyPass is not NULL) then
                                    proxyURL := REPLACE( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
                                    PIPE ROW( 'Proxy URL modified to include proxy user name and password' );
                            end if;
                            PIPE ROW( 'Proxy URL is '|| REPLACE(proxyURL,proxyPass,'*****') );
                            UTL_HTTP.set_proxy( proxyURL, C_NO_PROXY_FOR );
                    end if;
                    PIPE ROW( 'HTTP: GET '||url );
                    request := UTL_HTTP.begin_request( url, 'GET', UTL_HTTP.HTTP_VERSION_1_1 );
                    -- set HTTP header for the GET
                    UTL_HTTP.set_header( request, 'User-Agent', 'Mozilla/4.0 (compatible)' );
                    -- get response to the GET from web server
                    response := UTL_HTTP.get_response( request );
                    -- pipe the response as rows
                    endLoop := false;
                            exit when endLoop;
                                    UTL_HTTP.read_line( response, buffer, TRUE );
                                    if (buffer is not null) and length(buffer)>0 then
                                            PIPE ROW( buffer );
                                    end if;
                            exception when UTL_HTTP.END_OF_BODY then
                                    endLoop := true;
                    end loop;
                    UTL_HTTP.end_response( response );
            exception when OTHERS then
                    PIPE ROW( SQLERRM );
            show errors
            Edited by: Billy Verreynne on Sep 4, 2008 7:47 AM (old forum tags poorly supported by new Jive forum s/w)
            • 3. Re: Error using UTL_HTTP over HTTPS
              The WebBrowser() function is pipelined as it is intended to pipe debugging lines as part of the output. I prefer this method when testing code like this as the debug data and output data are integrated and can be read sequentially.

              In order to get HTTPS to work, the important steps are:
              1. create an Oracle wallet using owm (Oracle Wallet Manager) on the database server platform
              2. download the certificate from the HTTPS web server (in PEM format)
              3. upload this certificate using owm
              4. note that certificates can be chained and if such a certificate has a parent, that parent certificate also needs to be downloaded and imported into the wallet
              • 4. Re: Error using UTL_HTTP over HTTPS
                Thank you Billy, with your code i was able to find a solution...

                • 5. Re: Error using UTL_HTTP over HTTPS
                  Great stuff and thanks for the feedback.
                  • 6. Re: Error using UTL_HTTP over HTTPS
                    Another little question ? Is it possible to do an HTTPS connection using Oracle 9 ???

                    I tried it with an oracle 10 database but i have no result with oracle 9.2 !

                    Could you give me answer please ?

                    Thank you...

                    • 7. Re: Error using UTL_HTTP over HTTPS
                      > Is it possible to do an HTTPS connection using Oracle 9 ?

                      Do not know - we're using mostly 10G these days and the few 9i systems are not interfacing via HTTPS.

                      But I do not see why it will not work. UTL_HTTP is not exactly a new feature. As far as I recall, it was introduced back in 8i.

                      What errors do you get when using 9i?
                      • 8. Re: Error using UTL_HTTP over HTTPS
                        Hi Billy,

                        I am working on this also. How do I download the certificate from the HTTPS web server ?

                        • 9. Re: Error using UTL_HTTP over HTTPS
                          You should rather ask a question using a new thread - it is very easy to miss an update to an old thread like this one.

                          The easiest is to use your web browser, navigate to the HTTPS URL and accept the certificate. Your browser will save it for its own use. Most browsers have an option to export these certificates. I used Firefox and installed a PEM plugin (from https://addons.mozilla.org/en-US/firefox/addon/1964/) that allowed me to view and export a certificate in PEM format.

                          I then simply copy and pasted that into Oracle's Wallet Manager.
                          • 10. Re: Error using UTL_HTTP over HTTPS
                            Hi Billy,

                            I was trying to access a secure site that would give me the output in the XML format. The sample URL is something like this:

                            The following SQL:
                            select * from TABLE( webbrowser('https://xyz.oracle.com/users/1234.xml') ) where rownum < 11;

                            gives me the following output:

                            Setting browser configuration
                            Wallet set to file:<<wallet_path>>
                            Proxy Server is <<proxy_name>>
                            Proxy URL is http://<<proxy_name>>
                            HTTP: GET https://xyz.oracle.com/users/1234.xml
                            ORA-29268: HTTP client error 406 - Not Acceptable

                            6 rows selected.

                            When I access the same URL in the browser, I get the same 406 error. However, if I access it as https://xyz.oracle.com/users, it takes me to the Oracle Single Sign-On (SSO) page. Once I login to SSO and try access the complete URL (https://xyz.oracle.com/users/1234.xml), then I see the XML content.

                            Do you think this is to do something with setting the cookies or session before I access the URL in my code?

                            • 11. Re: Error using UTL_HTTP over HTTPS
                              Billy: Your webbrowser function is superb...extemely useful. Many thanks for posting.