This discussion is archived
8 Replies Latest reply: Jan 13, 2011 11:26 AM by 724532 Branched to a new discussion. RSS

Using UTL_HTTP for HTTPS

BillyVerreynne Oracle ACE
Currently Being Moderated
Oracle Enterprise 10.2.0.1 on a HP-UX box.

Wallet created and contains valid certificates.

From PL/SQL, I use the following call sequence:
   UTL_HTTP.Set_Response_Error_Check ( enable => TRUE );
   UTL_HTTP.Set_Detailed_Excp_Support ( enable => TRUE );

   UTL_HTTP.set_wallet( C_WALLET, C_WALLET_PASS );
 
   UTL_HTTP.set_proxy( proxyServer );
   request := UTL_HTTP.begin_request( url );

   UTL_HTTP.set_authentication(request, proxyUser, proxyPass, 'Basic', TRUE );                  
   
   response := UTL_HTTP.get_response( request );  <== ORA-06502 exception
   ..etc..
The error is a very strange:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_HTTP", line 1027


If I take out the Proxy Authentication and test the above code against a local web server (using HTTPS), it works as expected.

E.g. when testing locally and I use the incorrect wallet password, I get an appropriate error or if the wallet does not exist, I get an appropriate error (ORA-29106: Cannot import PKCS #12 wallet.).

If the certificate does not exist in the wallet, I get an appropriate error (ORA-29024: Certificate validation failure)

However, as soon as the Proxy is used, it fails with a PL/SQL: numeric or value error.

This seems to indicate the using a Proxy causes UTL_HTTP to fail before it even gets to the wallet processing, when the URL requested uses the HTTPS protocol. So the wallet setup and content do not seem to be issue.

I've done some stfw here, Google and Metalink. Could not find anything that sheds light on this, or even mentions the numeric/value error in the same breath of using the SET_WALLET call.

Unfortunately circumstances (at the moment) dictate that I must use a proxy. So I have no choice in that regard.

Will appreciate any (non void) pointers. :-)

Thanks.
  • 1. Re: Using UTL_HTTP for HTTPS
    Alessandro Rossi Journeyer
    Currently Being Moderated
    You get the wallet errors when you're using the connection not while you set its parameters and that is why if you can't connect throught the proxy you don't get errors about the wallet.

    The error about the proxy configuration anyway is quite strange to see inside a function accepting a string, so I think it could be a possible misshandled exceptioin.

    If you didn't do it yet,I suuggest you to verify if you can connect to that proxy with a simple utl_tcp.open_connection(), or that the string you passed is in the right format.

    If that works, I suppose you have enought reasons to open a TAR on metalink.

    Bye Alessandro
  • 2. Re: Using UTL_HTTP for HTTPS
    474972 Newbie
    Currently Being Moderated
    Hi !
    please give all your variables type ( request, proxyServer ... etc ).

    Try to use this in SQLPLUS ' replace the param values
    SELECT utl_http.request(p_url, p_authent_login || ':' || p_authent_password ||'@' || p_proxy,'file:' || p_wallet_path, p_wallet_pwd)
    FROM DUAL;

    This is a simple way to check if the WEB SERVER reponds in HTTPS.
    p_authent_login, p_authent_password -> is for your proxy

    Romeo
  • 3. Re: Using UTL_HTTP for HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > The error about the proxy configuration anyway is quite strange to see inside
    a function accepting a string, so I think it could be a possible misshandled
    exceptioin.

    Well, the command sequence is correct as far as UTL_HTTP documentation goes and numerous samples on the Net.

    It also makes sense ito how this works via web browsers :
    - first set the parameter (including the proxy to use)
    - start the request (e.g. type in the URL in the address bar and press enter)
    - the browser is challenged by the web server and pops up a Basic Auth username & password box
    - username and password are enterred and submitted
    - the web browser passes the that to the web server and the web proxy server accepts and executes the URL (contacts the actual server in the URL)

    So the following code sequence does not seem wrong to me:
      -- enter URL and submit (note that this goes to the proxy)
      request := UTL_HTTP.begin_request( url );  
      -- provide auth details to the proxy 
      UTL_HTTP.set_authentication(request, proxyUser, proxyPass, 'Basic', TRUE );                     
      -- the proxy now passes the request through to the actual web server and we get a response
       response := UTL_HTTP.get_response( request );
    This sequence btw works fine for HTTP.

    The only additional complexity is now adding another setting up front - the wallet. And making a HTTPS request via the proxy instead of a HTTP one.

    The basic auth is also for the proxy server. It challenges the web browser. Usually it will be the destination web server that challenges the web browser. According to the docs that is the purpose of the for_proxy parameter - which when set provide the response to the Basic Auth challenge to the proxy.

    All this works fine with HTTP...

    What puzzles me is that if I introduce an error on purpose using HTTPS, I get a meaningful error message - but only when not using a proxy. E.g. I use a wallet without the necessary certificate. I'm told that by UTL_HTTP.

    However, when the only change is to make use of a proxy, the error becomes a seemingly meanless number/value error.

    Which is why it seems to me that the error has something to do with the proxy and HTTPS combo - before it even gets to using the wallet.

    As for opening a TAR on Metalink.. I'm not sure how quick they will be able to provide a meaningful answer/workaround on this. After all, I expect that they do not have ready-to-use R&D environments to test HTTPS without a proxy, with a proxy using no authentication, and with a proxy using authentication.

    Am trying to set this up myself first to isolate the problem and determine if this is indeed a bug or not.
  • 4. Re: Using UTL_HTTP for HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    SELECT utl_http.request(p_url, p_authent_login || ':' || p_authent_password
    ||'@' || p_proxy,'file:' || p_wallet_path, p_wallet_pwd) FROM DUAL;
    Hmm.. I will try, but I doubt that this will solve the problem.

    The exact same sequence of code (including opening the wallet), works via the proxy to a website via HTTP.

    However, when the URL is changed from HTTP to HTTS, the funny error results. And this occurs before the proxy authentication. I.e.
       request := UTL_HTTP.begin_request( url );   <== FAILS HERE BEFORE AUTHENTICATION (and only when the URL is HTTPS)
       UTL_HTTP.set_authentication(request, proxyUser, proxyPass, 'Basic', TRUE);   
    I need to get a hole punched into the local firewall in order to test this code without a proxy. It seems to me that the combination of proxy and HTTPS is a problem for UTL_HTTP.

    What I do find a tad strange is this problem is not mentioned anywhere on Metalink, here in Oracle Forums, or elsewhere on the net. So either I'm doing something unique (unlikely), or am missing something else that is required to make UTL_HTTP work via a proxy using HTTPS.
  • 5. Re: Using UTL_HTTP for HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Yeehaa.. problem found - after using a ssh reverse tunnel to get direct access to the net.

    With the proxy eliminated, the error become apparent with the appropriate error message being displayed as oppose to the funny number/value error.

    Certificate chain was incomplete. Once the additional dependent certificates were installed, it is working fine using proxy authentication too.

    Hmm.. can consider this a bug of sorts. When using a proxy the exception ORA-06502: PL/SQL: numeric or value error is not trapped and returned as a ORA-29024: Certificate validation failure, as is the case when not using a proxy.
  • 6. Re: Using UTL_HTTP for HTTPS
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Arrghh.. spoke to soon. The combination of authenticated proxy and HTTPS using UTL_HTTP does not seem to work.

    Some more testing and then I will likely file a TAR on this.. sigh
  • 7. Re: Using UTL_HTTP for HTTPS
    454278 Newbie
    Currently Being Moderated
    Hi,

    Were you able to get a solution to this? I have a similar problem. I am trying to make a web call to an HTTPS url, using UTL_HTTP, and am getting the following error:

    ORA-29106: Cannot import PKCS #12 wallet.

    There is not much available on Google or Metalink.

    Ashish
  • 8. Re: Using UTL_HTTP for HTTPS
    724532 Newbie
    Currently Being Moderated
    I too am getting a similar problem. I'm in a situation where I'm trying to retrieve the authorization certificate from a thrid-party service. The only oracle documentation I can find claims (not verbatim) that the BER-encoding is malformed or unrecognized.

    I am currently looking into this. Any help would be much appreciated!

    Eric