This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Oct 12, 2012 10:37 AM by Rooney Go to original post RSS
  • 15. Re: How to call a web service in PL/SQL -failure to open file
    Rooney Newbie
    Currently Being Moderated
    Hi Billy,

    I created a wallet through Oracle Wallet Manager (Common Name = bl11.ussc.gov, gave organization name, city state, etc.)
    I got the certificate from the target site.
    I imported the trusted certificate.
    Made sure ewallet.p12 is in the wallet folder
    Doubled checked my passwords.

    But I am still getting the same error.

    This is what I have now, anything else I am missing .... thanks
         --// CREATE THE SOAP ENVELOPE
         SOAPENVELOPE := REPLACE(SOAP_ENVELOPE, '$SENT_ID', SENT_ID);
      
      -- set wallet for HTTPS access
      UTL_HTTP.SET_WALLET('file:c:\wallet', 'TerpsTerps');  
      
         --// MAKE THE POST CALL TO THE WEB SERVICE
         REQUEST := UTL_HTTP.BEGIN_REQUEST(SOAP_URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Type', 'text/xml; charset=utf-8');
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Length', LENGTH(SOAPENVELOPE));
         UTL_HTTP.SET_HEADER (REQUEST, 'SoapAction', 'http://www.datanomic.com/ws/BUSINESS_RULES');
         UTL_HTTP.WRITE_TEXT (REQUEST, SOAPENVELOPE);
         
         --// READ THE WEB SERVICE HTTP RESPONSE
         RESPONSE := UTL_HTTP.GET_RESPONSE(REQUEST);
  • 16. Re: How to call a web service in PL/SQL -failure to open file
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The error sounds like a permission issue - in other words, the Oracle server process executing your PL/SQL code attempts to open and read the wallet. This then fails.

    A couple of things to check and try.

    Was owm ran as the oracle o/s user (same user that owns ORACLE_HOME of the database instance)?

    Can UTL_FILE sample code (ran from the same Oracle schema as your https code) read the wallet file? (a directory object will be required for testing)

    Or you can use DBMS_LOB.LoadFromFile() to test read access to the wallet directory and file.

    Have there been subsequent changes ito file and directory ownerships and groups, subsequent to the db instance startup? (a change may not been seen by an existing process as it was created by the kernel using an older, and now outdated, security context - this can easily be seen when group ownership is modified on Unix as existing process environments do not know about these modifications)

    Have a look at Jeffrey's article on how to create and use an Oracle wallet.

    Have a look at support.oracle.com for notes or articles about the error that you are running into.

    We do not use Windows as o/s for any of our database servers (never have). So I'm not sure whether there are specific Windows-based issues that are causing your problem. The sample code and postings I referred you to, all deal with doing this on Linux.
  • 17. Re: How to call a web service in PL/SQL -failure to open file
    Rooney Newbie
    Currently Being Moderated
    Hi Billy,

    The link (http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_19.shtml) you provided is awesome.
    I followed it step by step.

    However, i am still getting
    Error report:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1130
    ORA-29024: Certificate validation failure
    ORA-06512: at line 10
    29273. 00000 -  "HTTP request failed"
    *Cause:    The UTL_HTTP package failed to execute the HTTP request.
    *Action:   Use get_detailed_sqlerrm to check the detailed error message.
               Fix the error and retry the HTTP request.
    The same link above provides information on how to Capture SSL Site Certificate. I got the certificate imported trusted certificate in my wallet, saved it, closed it, ran my function below.
    is there anything else I need to check for

    here is the code again:
    C_WALLET constant varchar2(4000) := 'file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/';
    C_WALLET_PASS constant varchar2(4000) := 'USSC#2012';
    
    --//LOCAL VARIABLES
    SOAPENVELOPE VARCHAR2(2000);
    REQUEST UTL_HTTP.REQ;
    RESPONSE UTL_HTTP.RESP;
    
    BEGIN
         --// CREATE THE SOAP ENVELOPE
         SOAPENVELOPE := REPLACE(SOAP_ENVELOPE, '$SENT_ID', SENT_ID);
      
      -- set wallet for HTTPS access
      UTL_HTTP.SET_WALLET(C_WALLET, C_WALLET_PASS);
      
         --// MAKE THE POST CALL TO THE WEB SERVICE
         REQUEST := UTL_HTTP.BEGIN_REQUEST(SOAP_URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Type', 'text/xml; charset=utf-8');
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Length', LENGTH(SOAPENVELOPE));
         UTL_HTTP.SET_HEADER (REQUEST, 'SoapAction', 'http://www.datanomic.com/ws/BUSINESS_RULES');
         UTL_HTTP.WRITE_TEXT (REQUEST, SOAPENVELOPE);
         
         --// READ THE WEB SERVICE HTTP RESPONSE
         RESPONSE := UTL_HTTP.GET_RESPONSE(REQUEST);
  • 18. Re: How to call a web service in PL/SQL -failure to open file
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Rooney,

    check if you are able to connect to the secure web site with a simple statement like this from SQLPlus or SQL Developer:
    EXEC UTL_HTTP.SET_WALLET('file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/', 'USSC#2012');
    SELECT UTL_HTTP.REQUEST('https://bl11.ussc.gov:7113/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl') FROM DUAL;
    {code}
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Oct 11, 2012 9:57 PM
    Rephrased the sentence                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 19. Re: How to call a web service in PL/SQL -failure to open file
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Rooney,

    I also forgot to ask you if you have completed the certificate chain in the Oracle Wallet.

    I'm sure Billy provided all information but here is also another link that you might find useful: Access to HTTPS via utl_http using the orapki wallet command

    Regards.
    Al
  • 20. How to call a web service in PL/SQL - ORA-19202: Error in XML processing
    Rooney Newbie
    Currently Being Moderated
    Hi Alberto/All,

    I have good news. I thank you all very much for the patients and help you guys provided.
    I was able to run the following code and SUCCESS, XML was returned. The only thing I changed was, I used http instead of https and changed the port number.
    EXEC UTL_HTTP.SET_WALLET('file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/', 'USSC#2012');
    SELECT UTL_HTTP.REQUEST('http://bl11.ussc.gov:7013/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl') FROM DUAL;
    I got back the full XML as you can see below:
    "<?xml version="1.0" encoding="UTF-8"?><!-- Published by JAX-WS RI at http://jax-ws.dev.java.net. RI's version is JAX-WS RI 2.1.1-b03-. --><!-- wsdl file generated Oct 11, 2012 9:45 AM --><wsdl:definitions xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://www.datanomic.com/ws" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.datanomic.com/ws">
      <wsdl:types>
        <xs:schema elementFormDefault="qualified" targetNamespace="http://www.datanomic.com/ws">
          <xs:element name="request">
            <xs:complexType>
              <xs:sequence>
                <xs:element maxOccurs="unbounded" minOccurs="0" name="record">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element minOccurs="0" name="SENT_ID" type="xs:decimal"></xs:element>
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
              <xs:attribute name="id" type="xs:string"></xs:attribute>
            </xs:complexType>
          </xs:element>
          <xs:element name="response">
            <xs:complexType>
              <xs:sequence>
                <xs:element maxOccurs="unbounded" minOccurs="0" name="record">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element minOccurs="0" name="FINAL_MESSAGE" type="xs:string"></xs:element>
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
              <xs:attribute name="id" type="xs:string"></xs:attribute>
            </xs:complexType>
          </xs:element>
        </xs:schema>
      </wsdl:types>
      <wsdl:message name="Request">
        <wsdl:part element="tns:request" name="body"></wsdl:part>
      </wsdl:message>
      <wsdl:message name="Response">
        <wsdl:part element="tns:response" name="body"></wsdl:part>
      </wsdl:message>
      <wsdl:portType name="PortType">
        <wsdl:operation name="process">
          <wsdl:input message="tns:Request"></wsdl:inp"
    I am getting some other error related to XML parsing, it looks like parsing is not working correctly.

    DB version
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    "CORE     11.2.0.3.0     Production"
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    Connecting to the database BL27_USSC_CASES.
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00229: input source is empty
    ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 74
    ORA-06512: at line 7
    Process exited.
    Disconnecting from the database BL27_USSC_CASES.
    here is my function:
    create or replace
    FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
    
    --//URL CALL
    SOAP_URL CONSTANT VARCHAR2(1000) := 'http://bl11.ussc.gov:7013/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl';
    
    --// SOAP ENVELOPE TEMPLATE, CONTAINING $ SUBSTITUTION VARIABLES
    SOAP_ENVELOPE CONSTANT VARCHAR2(2000) := 
    '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://www.datanomic.com/ws">
       <soapenv:Header/>
       <soapenv:Body>
          <ws:request id="?">
             <!--Zero or more repetitions:-->
             <ws:record>
                <!--Optional:-->
                <ws:SENT_ID>$SENT_ID</ws:SENT_ID>
             </ws:record>
          </ws:request>
       </soapenv:Body>
    </soapenv:Envelope>';
    
    C_WALLET constant varchar2(4000) := 'file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/';
    C_WALLET_PASS constant varchar2(4000) := 'USSC#2012';
    
    --//LOCAL VARIABLES
    SOAPENVELOPE VARCHAR2(2000);
    REQUEST UTL_HTTP.REQ;
    RESPONSE UTL_HTTP.RESP;
    BUFFER VARCHAR2(32767);
    SOAPRESPONSE CLOB;
    XMLRESPONSE XMLTYPE;
    EOF BOOLEAN;
    
    BEGIN
         --// CREATE THE SOAP ENVELOPE
         SOAPENVELOPE := REPLACE(SOAP_ENVELOPE, '$SENT_ID', SENT_ID);
      
      -- set wallet for HTTPS access
      UTL_HTTP.SET_WALLET(C_WALLET, C_WALLET_PASS);
      
         --// MAKE THE POST CALL TO THE WEB SERVICE
         REQUEST := UTL_HTTP.BEGIN_REQUEST(SOAP_URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Type', 'text/xml; charset=utf-8');
         UTL_HTTP.SET_HEADER (REQUEST, 'Content-Length', LENGTH(SOAPENVELOPE));
         UTL_HTTP.SET_HEADER (REQUEST, 'SoapAction', 'http://www.datanomic.com/ws/BUSINESS_RULES');
         UTL_HTTP.WRITE_TEXT (REQUEST, SOAPENVELOPE);
         
         --// READ THE WEB SERVICE HTTP RESPONSE
         RESPONSE := UTL_HTTP.GET_RESPONSE(REQUEST);
         DBMS_LOB.CREATETEMPORARY(SOAPRESPONSE, TRUE);
         EOF := FALSE;
         
         LOOP
              EXIT WHEN EOF;
              BEGIN
                   UTL_HTTP.READ_LINE(RESPONSE, BUFFER, TRUE);
                   IF LENGTH(BUFFER) > 0 THEN
                        DBMS_LOB.WRITEAPPEND(SOAPRESPONSE, LENGTH(BUFFER), BUFFER);
                   END IF;
                   EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN 
                   EOF := TRUE;
              END;
         END LOOP;
      UTL_HTTP.END_RESPONSE(RESPONSE);
         
         --// AS THE SOAP RESPONDS WITH XML, WE CONVERT THE RESPONSE TO XML
      XMLRESPONSE := XMLTYPE(SOAPRESPONSE);
      DBMS_LOB.FREETEMPORARY(SOAPRESPONSE); 
         RETURN(XMLRESPONSE);
         
    EXCEPTION WHEN OTHERS THEN
         IF SOAPRESPONSE IS NOT NULL THEN
        DBMS_LOB.FREETEMPORARY(SOAPRESPONSE);
         END IF;                              // THIS IS LINE 74
         RAISE;
    
    END CALL_EDQ_WEBSERVICE;
    Thanks.

    Edited by: Rooney on Oct 12, 2012 10:23 AM
  • 21. Re: How to call a web service in PL/SQL - ORA-19202: Error in XML processing
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) What is line 74 of your procedure? Is that the
      XMLRESPONSE := XMLTYPE(SOAPRESPONSE);
    call? Or something else?

    2) The XML you posted appears to be truncated. Is that a copy and paste error and/or a limit on whatever tool you are using to display the XML? Or is the actual XML getting returned truncated?

    Justin
  • 22. Re: How to call a web service in PL/SQL - ORA-19202: Error in XML processing
    Rooney Newbie
    Currently Being Moderated
    hi Justin,

    END IF; // THIS IS LINE 74
    RAISE;

    Also just added a comment where line 74 is located in the code provided above.
    I used SOAP to generate the XML.

    In EDQ we can right click on the webservice we created and "Copy WSDL URL to Clipboard". So i did that, entered the URL in SOAP and generated the XML you see in the code above. I did copy and paste. I double checked the XML and nothing is missing.

    Thanks.

    I was able to use the debugger, and found out RESPONSE := UTL_HTTP.GET_RESPONSE(REQUEST); is returning nothing , null.
    So I am not able to get the response, so it jumps to the Exception right away. Line 74.

    the debugger mentioned the following:

    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1369
    ORA-29259: end-of-input reached
    ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 74
    ORA-06512: at line 7

    So i am assuming i need to authenticate here ?

    Thanks

    Edited by: Rooney on Oct 12, 2012 12:15 PM
  • 23. Re: How to call a web service in PL/SQL - ORA-19202: Error in XML processing
    Rooney Newbie
    Currently Being Moderated
    ok I found the solution to this.
    You need to authenticate for it to work.

    you need to set webservices.authentication=none in director.properties file on the server and then restart EDQ App server.
    This will turn off authentication altogethe

    Thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points