1 2 Previous Next 23 Replies Latest reply: Oct 12, 2012 12:37 PM by Rooney Go to original post RSS
      • 15. Re: How to call a web service in PL/SQL -failure to open file
        Rooney
        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
          Billy~Verreynne
          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
            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
              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
                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
                  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
                    JustinCave
                    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
                      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
                        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