How to properly authenticate against HTTP servers with BASIC authentication using UTL_HTTP API (Doc ID 2178493.1)

Last updated on MARCH 04, 2022

Applies to:

Goal

 With the introduction of the utl_http package, pl/sql developers can now perform POST and GET operations to Web Servers.  Essentially this API makes the database into a web client much like a browser.  With the progression of web servers, more and more are using authentication schemes. This note will focus on web servers that require BASIC authentication. (Note: Although this API can be used for Web Services (REST and SOAP), we recommend the Java based UTL_DBWS API but this note will focus on the integrated C based UTL_HTTP API.)

In order to communicate with a web server, you must make a request. That http request must contain things like the URL, the method, the protocol and version, SSL certificate for HTTPS urls, encoding, and in some cases the username and password for authentication. There are 3 common functions in the UTL_HTTP package for building requests: utl_http.request, utl_http.request_pieces, and utl_http.begin_request.

The first one is typically used in a select statement without using any of the other API calls. It only returns the first 2000 characters of the response. Most everything part of a request is defaulted so this function is used for the most basic of web servers and only in cases where the first 2000 characters of the response are sufficient.

eg: select utl_http.request('www.oracle.com') from dual;

The second function is much like the first but it returns the entire response in 2000 byte pieces. This too is not really useful in more complicated http server requests. You can take advantage of the many defaulted request settings but you then need to loop thru the pieces to build the response.

The 3rd and most useful request function is utl_http.begin_request. Where the first 2 functions actually build the request and send it to the http server and then receive the response all-in-one step, this function only starts the building of the request. It is the most versatile and allows the programmer to build complex requests using other functions such as set_header and set_authentication before getting the response. Also, even though all 3 can pass the authentication information in the URL as part of the request, there are security issues with passing this to the first URL in the request.  Since the first 2 functions automatically get the response, and that response might actually be a redirect this 3rd method is the preferred way to communicate with web servers that require BASIC authentication.  Also, although one can still use set_authentication with any of the 3 request functions, and not embed this info in the URL, it is not very secure to send it with the first 2 because many servers can redirect or be temporarily moved. Because of that and potential issues with passing the authentication information in the URL, this note will use this 3rd function to communicate with http servers that require BASIC authentication. 

Note: This note does not apply to HTTPS URLs and it assumes the ACLs have been granted.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.