This discussion is archived
11 Replies Latest reply: Jun 3, 2009 3:01 AM by 536256 RSS

Error using UTL_HTTP over HTTPS

455963 Newbie
Currently Being Moderated
Hello,

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 ?

Thanks...

LIB
  • 1. Re: Error using UTL_HTTP over HTTPS
    585236 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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;
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    Setting browser configuration
    Wallet set to file:/etc/ORACLE/WALLETS/oracle/
    Proxy Server is 198.54.206.99
    Proxy URL modified to include proxy user name and password
    Proxy URL is http://verreyb:*****@198.54.206.99
    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
    --
    -- BASIC PL/SQL WEB BROWSER TEMPLATE
    -- 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) := '198.54.206.99';
            -- 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;
    begin
            -- 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;
            loop
                    exit when endLoop;
    
                    begin
                            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;
    
            end loop;
            UTL_HTTP.end_response( response );
    
            return;
    
    exception when OTHERS then
            PIPE ROW( SQLERRM );
    end;
    /
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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
    455963 Newbie
    Currently Being Moderated
    Thank you Billy, with your code i was able to find a solution...

    LIB
  • 5. Re: Error using UTL_HTTP over HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Great stuff and thanks for the feedback.
  • 6. Re: Error using UTL_HTTP over HTTPS
    455963 Newbie
    Currently Being Moderated
    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...

    LIB
  • 7. Re: Error using UTL_HTTP over HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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
    481897 Newbie
    Currently Being Moderated
    Hi Billy,

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

    Thanks!
  • 9. Re: Error using UTL_HTTP over HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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
    515456 Newbie
    Currently Being Moderated
    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:
    https://xyz.oracle.com/users/1234.xml

    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?

    Regards,
    Aditya
  • 11. Re: Error using UTL_HTTP over HTTPS
    536256 Newbie
    Currently Being Moderated
    Billy: Your webbrowser function is superb...extemely useful. Many thanks for posting.