Home » SQL & PL/SQL » SQL & PL/SQL » Error ocurred calling a Web Service using UTL_HTTP (Oracle Database, 10g Enterprise Edition Release 10.2.0.4.0, HP-UX)
Error ocurred calling a Web Service using UTL_HTTP [message #588741] Thu, 27 June 2013 18:40 Go to next message
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 #588743 is a reply to message #588741] Thu, 27 June 2013 19:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I have a couple of quick thoughts that may help or be a waste of time.
1) https uses a different port than plain http. a Firewall could be blocking & result in an error
2) consider packet sniffing to see what new clues you may learn.
3) can you test or validate the Oracle wallet configuration & operation, independent of the WebService?
Re: Error ocurred calling a Web Service using UTL_HTTP [message #588746 is a reply to message #588743] Thu, 27 June 2013 22:55 Go to previous messageGo to next message
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.
Re: Error ocurred calling a Web Service using UTL_HTTP [message #597985 is a reply to message #588746] Wed, 09 October 2013 09:49 Go to previous messageGo to next message
chris_a
Messages: 2
Registered: October 2013
Junior Member
Hi Gusmen,

Did you find a solution to this? I'm struggling with a very similar problem.

Thanks,
Chris
Re: Error ocurred calling a Web Service using UTL_HTTP [message #597997 is a reply to message #597985] Wed, 09 October 2013 12:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Chris,

OP is yet to come back with the suggested workaround. You too might have to wait or create your own topic, if you did not find any solution after searching.

Regards,
Lalit
Re: Error ocurred calling a Web Service using UTL_HTTP [message #609812 is a reply to message #597997] Wed, 12 March 2014 10:48 Go to previous messageGo to next message
gusmenp
Messages: 6
Registered: June 2013
Location: Caracas
Junior Member
Hi everybody, sorry for the late.

We found the solution for this problem in MyOracleSupport. This problem is a bug until the version 11.1.0.6, and was resolved definitely in the version 11.2. The bug is the 6973000: UTL_HTTP DOES NOT SUPPORT TRANSPORT LAYER SECURITY VERSION 1 (TLSV1) and occurs when UTL_HTTP tries to connect to a HTTPS website that uses TLSv1 protocol, and like the bug's title says, UTL_HTTP doesn't support TLS Version 1.

For that reason in the installation on my personal computer works fine, because it is 11.2.0.1.0.

The solution for a minor version of 11.2 is install the patch that resolve the bug 6973000.

Regards,

Gusmen.
Re: Error ocurred calling a Web Service using UTL_HTTP [message #684823 is a reply to message #588746] Tue, 31 August 2021 07:37 Go to previous message
mohsin0012
Messages: 1
Registered: August 2021
Junior Member
Dear ,

i need help..how to configure SSL..


Error executing statement: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1029 ORA-28860: Fatal SSL error ORA-06512:

Previous Topic: Duplicating Excel "RATE" function in Oracle
Next Topic: Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “
Goto Forum:
  


Current Time: Sat Nov 27 20:27:35 CST 2021