Not like non-SSL (http) web site, accessing SSL (https) web sites with UTL_HTTP needs additional configuration steps outside of PL/SQL programming as following.
1. Create wallet on database server where UTL_HTTP is called from
$ $ORACLE_HOME/bin/orapki wallet create -wallet /u01/app/oracle/admin/DB01/wallet -auto_login -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
If -auto_login option is not used, UTL_HTTP.SET_WALLET has to be called with the wallet password (here given Password11203). Check wallet built-in certificates:
$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
2. Import certificate of the web sites
Certificate file GoogleAccount.cer is exported from FireFox while https://account.google.com is open. If you do not know how to export certificate of your SSL web sites, try to GOOGLE with "export ssl certificate from Internet Explorer Firefox".
$ $ORACLE_HOME/bin/orapki wallet add -wallet /u01/app/oracle/admin/DB01/wallet -trusted_cert -cert /tmp/GoogleAccount.cer -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
The root certificate (CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US) of https://account.google.com is imported successfully.
3. Test UTL_HTTP
system@DB01> DECLARE
2 v_http_request UTL_HTTP.REQ;
3 v_http_response UTL_HTTP.RESP;
4 v_text VARCHAR2(255);
5 BEGIN
6 --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port'); -- if proxy is needed
7 UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
8 v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
9 v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
10 UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
11 DBMS_OUTPUT.PUT_LINE (v_text);
12 UTL_HTTP.END_RESPONSE(v_http_response);
13 END;
14 /
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta content="width=300, init
PL/SQL procedure successfully completed.
2 v_http_request UTL_HTTP.REQ;
3 v_http_response UTL_HTTP.RESP;
4 v_text VARCHAR2(255);
5 BEGIN
6 --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port'); -- if proxy is needed
7 UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
8 v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
9 v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
10 UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
11 DBMS_OUTPUT.PUT_LINE (v_text);
12 UTL_HTTP.END_RESPONSE(v_http_response);
13 END;
14 /
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta content="width=300, init
PL/SQL procedure successfully completed.
If the wallet is created without '-auto_login' option, UTL_HTTP.SET_WALLET has to be called as UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet','Pasword11203').Here, 'Pasword11203' is password of the wallet.If the wallet is not configured properly or required certificate is not imported into wallet, UTL_HTTP will return 'ORA-29024: Certificate validation failure',
system@DB01> DECLARE
2 v_http_request UTL_HTTP.REQ;
3 v_http_response UTL_HTTP.RESP;
4 v_text VARCHAR2(255);
5 BEGIN
6 --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port'); -- if proxy is needed
7 --UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
8 v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
9 v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
10 UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
11 DBMS_OUTPUT.PUT_LINE (v_text);
12 UTL_HTTP.END_RESPONSE(v_http_response);
13 END;
14 /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 8
2 v_http_request UTL_HTTP.REQ;
3 v_http_response UTL_HTTP.RESP;
4 v_text VARCHAR2(255);
5 BEGIN
6 --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port'); -- if proxy is needed
7 --UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
8 v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
9 v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
10 UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
11 DBMS_OUTPUT.PUT_LINE (v_text);
12 UTL_HTTP.END_RESPONSE(v_http_response);
13 END;
14 /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 8
No comments:
Post a Comment