This content has been marked as final. Show 11 replies
I've written a basic PL/SQL web browser template. It looks/works as follows:
The source code:
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 184.108.40.206 Proxy URL modified to include proxy user name and password Proxy URL is http://verreyb:*****@220.127.116.11 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.
Edited by: Billy Verreynne on Sep 4, 2008 7:47 AM (old forum tags poorly supported by new Jive forum s/w)
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) := '18.104.22.168'; -- 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
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
> 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?
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.
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?