Error ocurred calling a Web Service using UTL_HTTP [message #588741] |
Thu, 27 June 2013 18:40 |
|
gusmenp
Messages: 6 Registered: June 2013 Location: Caracas
|
Junior Member |
|
|
Hi,
I'm calling a web service with HTTPS protocol from a pl/sql program and i'm using the UTL_HTTP package for do this.
I have a Oracle DB installation 11g Enterprise Edition Release 11.2.0.1.0 on my personal computer with a Windows XP OS, and when i run the code it works fine and returns the result I expect. But, when i run the same code on the Oracle DB of my work (Oracle 10g Enterprise Edition Release 10.2.0.4.0 and OS HP-UX) it fails and give me an error, i think that the error has to do with the configuration of the wallet and the certificates in it, because i consume the WS from Os prompt using the command WGET and works fine and i test consuming a WS with HTTP no secure and works fine too.
This is the code and the result in the Oracle DB 11g on my computer:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as oe@oRCL
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
4 v_resp utl_http.resp;
5 v_msg varchar2(80);
6 v_entire_msg varchar2(32767) := null;
7 v_conversion_factor number;
8 v_url varchar2(2000) := 'https://www.datacredito.com.ve:8880/dhwsrp/services';
9 begin
10 utl_http.set_wallet('file:S:\app\product\11.2.0\dbhome_1\wallet',
11 'DataCredito2013');
12 v_req := utl_http.begin_request(url => v_url, method => 'GET');
13 v_resp := utl_http.get_response(r => v_req);
14
15 begin
16 loop
17 utl_http.read_text(r => v_resp, data => v_msg);
18 v_entire_msg := v_entire_msg || v_msg;
19 end loop;
20 exception
21 when utl_http.end_of_body then
22 null;
23 end;
24 utl_http.end_response(r => v_resp);
25
26 dbms_output.put_line('Result: ');
27 dbms_output.put_line(v_entire_msg);
28 end;
29 /
Result:
<h2>And now... Some Services</h2>
<ul>
<li>DHService <a href="https://www.datacredito.com.ve:8880/dhwsrp/services/DHService?wsdl"><i>(wsdl)</i></a></li>
<ul>
<li>consultar
<li>consultar
<li>customConsultar
</ul>
</ul>
PL/SQL procedure successfully completed
SQL>
And this is the code(Is the same, the only difference is the path of the wallet) and the error resultant:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as gpacheco@acseld
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
4 v_resp utl_http.resp;
5 v_msg varchar2(80);
6 v_entire_msg varchar2(32767) := null;
7 v_conversion_factor number;
8 v_url varchar2(2000) := 'https://www.datacredito.com.ve:8880/dhwsrp/services';
9 begin
10 utl_http.set_wallet('file:/opt/app/oracle/product/wallet','DataCredito2013');
11 v_req := utl_http.begin_request(url => v_url, method => 'GET');
12 v_resp := utl_http.get_response(r => v_req);
13
14 begin
15 loop
16 utl_http.read_text(r => v_resp, data => v_msg);
17 v_entire_msg := v_entire_msg || v_msg;
18 end loop;
19 exception
20 when utl_http.end_of_body then
21 null;
22 end;
23 utl_http.end_response(r => v_resp);
24
25 dbms_output.put_line('Result: ');
26 dbms_output.put_line(v_entire_msg);
27 end;
28 /
declare
v_debug_mode boolean := false;
v_req utl_http.req;
v_resp utl_http.resp;
v_msg varchar2(80);
v_entire_msg varchar2(32767) := null;
v_conversion_factor number;
v_url varchar2(2000) := 'https://www.datacredito.com.ve:8880/dhwsrp/services';
begin
utl_http.set_wallet('file:/opt/app/oracle/product/wallet','DataCredito2013');
v_req := utl_http.begin_request(url => v_url, method => 'GET');
v_resp := utl_http.get_response(r => v_req);
begin
loop
utl_http.read_text(r => v_resp, data => v_msg);
v_entire_msg := v_entire_msg || v_msg;
end loop;
exception
when utl_http.end_of_body then
null;
end;
utl_http.end_response(r => v_resp);
dbms_output.put_line('Result: ');
dbms_output.put_line(v_entire_msg);
end;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-28860: Fatal SSL error
ORA-06512: at line 11
SQL>
I have days looking for a solution but could not find it, I appreciate if someone can help me.
Gusmen.
|
|
|
|
Re: Error ocurred calling a Web Service using UTL_HTTP [message #588746 is a reply to message #588743] |
Thu, 27 June 2013 22:55 |
|
gusmenp
Messages: 6 Registered: June 2013 Location: Caracas
|
Junior Member |
|
|
Hi BlackSwan, thanks for your reply.
Here my comments for your thoughts:
1) Like i said in the first text, i consume the WS from OS prompt using the HP-UX command WGET and it works fine. I think if a Firewall is blocking then i could not consume the WS for this way.
2) Tomorrow i do this and let you know what i get.
3) For test what you told me in this point, i run the same code with other WS, here the url:
https://stage.e-verify.uscis.gov/WebService/EmployerWebServiceV23.asmx?wsdl(https://stage.e-verify.uscis.gov/WebService/EmployerWebServiceV23.asmx?wsdl
And in this case it works ok. Here the code (is the same but with the new url):
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as gpacheco@acseld
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
4 v_resp utl_http.resp;
5 v_msg varchar2(80);
6 v_entire_msg varchar2(32767) := null;
7 v_conversion_factor number;
8 --v_url varchar2(2000) := 'https://www.datacredito.com.ve:8880/dhwsrp/services';
9 v_url varchar2(2000) := 'https://stage.e-verify.uscis.gov/WebService/EmployerWebServiceV23.asmx?wsdl(https://stage.e-verify.uscis.gov/WebService/EmployerWebServiceV23.asmx?wsdl';
10 begin
11 utl_http.set_wallet('file:/opt/app/oracle/product/wallet','DataCredito2013');
12 v_req := utl_http.begin_request(url => v_url, method => 'GET');
13 v_resp := utl_http.get_response(r => v_req);
14
15 begin
16 loop
17 utl_http.read_text(r => v_resp, data => v_msg);
18 v_entire_msg := v_entire_msg || v_msg;
19 end loop;
20 exception
21 when utl_http.end_of_body then
22 null;
23 end;
24 utl_http.end_response(r => v_resp);
25
26 dbms_output.put_line('Result: ');
27 dbms_output.put_line(v_entire_msg);
28 end;
29 /
Result:
<html>
<head><link rel="alternate" type="text/xml" href="/WebService/EmployerWebServiceV23.asmx?disco" />
<style type="text/css">
BODY { color: #000000; background-color: white; font-family: Verdana; margin-left: 0px; margin-top: 0px; }
#content { margin-left: 30px; font-size: .70em; padding-bottom: 2em; }
A:link { color: #336699; font-weight: bold; text-decoration: underline; }
A:visited { color: #6699cc; font-weight: bold; text-decoration: underline; }
A:active { color: #336699; font-weight: bold; text-decoration: underline; }
A:hover { color: cc3300; font-weight: bold; text-decoration: underline; }
P { color: #000000; margin-top: 0px; margin-bottom: 12px; font-family: Verdana; }
pre { background-color: #e5e5cc; padding: 5px; font-family: Courier New; font-size: x-small; margin-top: -5px; border: 1px #f0f0e0 solid; }
td { color: #000000; font-family: Verdana; font-size: .7em; }
h2 { font-size: 1.5em; font-weight: bold; margin-top: 25px; margin-bottom: 10px; border-top: 1px solid #003366; margin-left: -15px; color: #003366; }
h3 { font-size: 1.1em; color: #000000; margin-left: -15px; margin-top: 10px; margin-bottom: 10px; }
ul { margin-top: 10px; margin-left: 20px; }
ol { margin-top: 10px; margin-left: 20px; }
li { margin-top: 10px; color: #000000; }
font.value { color: darkblue; font: bold; }
font.key { color: darkgreen; font: bold; }
font.error { color: darkred; font: bold; }
.heading1 { color: #ffffff; font-family: Tahoma; font-size: 26px; font-weight: normal; background-color: #003366; margin-top: 0px; margin-bottom: 0px; margin-left: -30px; padding-top: 10px; padding-bottom: 3px; padding-left: 15px; width: 105%; }
.button { background-color: #dcdcdc; font-family: Verdana; font-size: 1em; border-top: #cccccc 1px solid; border-bottom: #666666 1px solid; border-left: #cccccc 1px solid; border-right: #666666 1px solid; }
.frmheader { color: #000000; background: #dcdcdc; font-family: Verdana; font-size: .7em; font-weight: normal; border-bottom: 1px solid #dcdcdc; padding-top: 2px; padding-bottom: 2px; }
.frmtext { font-family: Verdana; font-size: .7em; margin-top: 8px; margin-bottom: 0px; margin-left: 32px; }
.frmInput { font-family: Verdana; font-size: 1em; }
.intro { margin-left: -15px; }
</style>
<title>
EmployerWebServiceV23 Web Service
</title></head>
<body>
<div id="content">
<p class="heading1">EmployerWebServiceV23</p><br>
<span>
<p class="intro">The following operations are supported. For a formal definition, please review the <a href="EmployerWebServiceV23.asmx?WSDL">Service Description</a>. </p>
<ul>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpAckReceiptOfResolvedCaseNbr">EmpAckReceiptOfResolvedCaseNbr</a>
<span>
<br>This method is used to signal that a client has successfully received secondary information for a case.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpCitDHSReVerify">EmpCitDHSReVerify</a>
<span>
<br>This method is used to retry an initial verification after a DHS error for a U.S Citizen.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpCloseCase">EmpCloseCase</a>
<span>
<br>This method is used to close a case.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpConfirmPhoto">EmpConfirmPhoto</a>
<span>
<br>This method is called to confirm a photo received in an initial response.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpCpsVerifyConnection">EmpCpsVerifyConnection</a>
<span>
<br>This method is used to verify a userid/password and connection to cps.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpDHSReVerify">EmpDHSReVerify</a>
<span>
<br>This method is used to retry an initial verification after a DHS error.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpDMVDHSReVerify">EmpDMVDHSReVerify</a>
<span>
<br>This method is used to retry an initial verification after a DHS error.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetAlertCases">EmpGetAlertCases</a>
<span>
<br>This method returns all of the cases for a particular alert type
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetAllDataFields">EmpGetAllDataFields</a>
<span>
<br>This method returns all of the valid citizenship codes and descriptions
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetAvailableDocumentTypes">EmpGetAvailableDocumentTypes</a>
<span>
<br>This method returns all of the valid citizenship codes and descriptions
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetCaseAlertCounts">EmpGetCaseAlertCounts</a>
<span>
<br>This method returns case alerts
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetCaseDetails">EmpGetCaseDetails</a>
<span>
<br>This method is used to retrieve detail information on a case.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetCitizenshipStatusCodes">EmpGetCitizenshipStatusCodes</a>
<span>
<br>This method returns all of the valid citizenship codes and descriptions
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetClosureCodes">EmpGetClosureCodes</a>
<span>
<br>This method returns all of the valid closure codes for a particular case
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetIssuingAuthorities">EmpGetIssuingAuthorities</a>
<span>
<br>This method returns all of the valid issuing authorities for a document id
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetListBCDocumentTypes">EmpGetListBCDocumentTypes</a>
<span>
<br>This method returns all of the valid List B and C document ids for a citizenship code
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetNextResolvedCaseNbrs">EmpGetNextResolvedCaseNbrs</a>
<span>
<br>This method is used to retrieve the next First, Second, or Third case number for a case that has pending DHS additional information ready.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpGetSupportingDocumentTypes">EmpGetSupportingDocumentTypes</a>
<span>
<br>This method returns all of the supporting documents for an issuingauthority
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpInitBPVerif">EmpInitBPVerif</a>
<span>
<br>This method is the (BP)employer combined initial/additional (primary/secondary) transaction.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpInitDABPVerif">EmpInitDABPVerif</a>
<span>
<br>This method is the (DABP)employer combined initial/additional (primary/secondary) transaction.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpRetrievePhoto">EmpRetrievePhoto</a>
<span>
<br>This method is used to retrieve photos in need of confirmation.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpSSAReVerify">EmpSSAReVerify</a>
<span>
<br>This method is used to retry an initial verification after an SSA TNC.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpSSAResubmittal">EmpSSAResubmittal</a>
<span>
<br>This method is used to resubmit an employment verification request that previously returned SSA TENTATIVE NONCONFIRMATION
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpSubDHSReferral">EmpSubDHSReferral</a>
<span>
<br>This Web Service method is used to notify DHS than an employee plans to manually contest an Initial Verification response.
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpSubSSAReferral">EmpSubSSAReferral</a>
<span>
<br>This method is used to notify CPS that an employee was referred to the Social Security Administration to resolve an SSN issue
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpSubmitAdditVerif">EmpSubmitAdditVerif</a>
<span>
<br>This method is used submit additional(secondary) data to the DHS FCO
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpUpdateDHSLetterReceived">EmpUpdateDHSLetterReceived</a>
<span>
<br>This method sets the date that a DHS letter is given to an employee
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=EmpUpdateSSALetterReceived">EmpUpdateSSALetterReceived</a>
<span>
<br>This method sets the date that an SSA letter is given to an employee
</span>
</li>
<p>
<li>
<a href="EmployerWebServiceV23.asmx?op=SetUserPassword">SetUserPassword</a>
<span>
<br>This method is used to reset a users password.
</span>
</li>
<p>
</ul>
</span>
<span>
</span>
</body>
</html>
PL/SQL procedure successfully completed
SQL>
Thanks for your help.
Gusmen.
|
|
|
|
|
|
|