1 2 Previous Next 23 Replies Latest reply: Oct 12, 2012 12:37 PM by Rooney RSS

    How to call a web service in PL/SQL

    Rooney
      Hi All,

      I just created a web service in EDQ (Enterprise Data Quality), and would like to test it through a PL/SQL procedure.
      What i need help on is how to call a web service from PL/SQL especially to EDQ. My procedure will take in an ID as a parameter.

      Thanks in advance,
      create or replace procedure call_edq_webservice (ID number) as .....
        • 1. Re: How to call a web service in PL/SQL
          sb92075
          Rooney wrote:
          Hi All,

          I just created a web service in EDQ (Enterprise Data Quality), and would like to test it through a PL/SQL procedure.
          What i need help on is how to call a web service from PL/SQL especially to EDQ. My procedure will take in an ID as a parameter.

          Thanks in advance,
          create or replace procedure call_edq_webservice (ID number) as .....
          http://www.lmgtfy.com/?q=oracle+How+to+call+a+web+service+in+PL/SQL
          • 2. Re: How to call a web service in PL/SQL
            Rooney
            ok so here is what i have so far, However i do need help on calling the service and returning the results.

            here is my code:
            I am new to calling websevices from PL/SQL. Its my first time.
            CREATE OR REPLACE FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN VARCHAR2 IS
            
            L_ENVELOPE   CLOB;
            L_XML        XMLTYPE;
            L_URL        VARCHAR2(200);
            CALL_        SYS.UTL_DBWS.CALL;
            
            BEGIN
              
              L_URL := 'https://bl11.ussc.gov:7113/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl';
              L_ENVELOPE := '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                                     <soap:Header/>
                                     <soap:Body>
                                          <dn:request xmlns:dn="http://www.datanomic.com/ws" id="?">
                                               <dn:record>
                                                    <dn:SENT_ID>' || SENT_ID || '</dn:SENT_ID>
                                               </dn:record>
                                          </dn:request>
                                     </soap:Body>
                                 </soap:Envelope>';                    
                 
              // is this correct on how to invoke the webservice
              L_XML := SYS.UTL_DBWS.INVOKE(CALL_, L_ENVELOPE);
            
            // my webservice returns a message, such as success or fail ...how can I return the response.
              
            END CALL_EDQ_WEBSERVICE;
            Thanks you very much.
            • 3. Re: How to call a web service in PL/SQL
              Sven W.
              I think you are almost done.
              Just use xmltype as return type and add a final statement "return L_XML;".

              If the webservice call was successfull then the XML is returned. If not then either the return code is null or you'll get an exception.
              CREATE OR REPLACE FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
               
              ...
                L_XML := UTL_DBWS.INVOKE(CALL_, L_ENVELOPE);
               
              // my webservice returns a message, such as success or fail ...how can I return the response.
                return l_xml;
              END CALL_EDQ_WEBSERVICE;
              What is still missing is how the url handle is involved. Usually you need to establish a service factory first. And after the invokation you need to release this factory handle.

              See this example http://www.oracle-base.com/articles/10g/utl_dbws-10g.php

              Edited by: Sven W. on Oct 4, 2012 10:20 PM
              • 4. Re: How to call a web service in PL/SQL
                Billy~Verreynne
                Rooney wrote:

                I just created a web service in EDQ (Enterprise Data Quality), and would like to test it through a PL/SQL procedure.
                What i need help on is how to call a web service from PL/SQL especially to EDQ. My procedure will take in an ID as a parameter.
                Am using web services fairly often often - I prefer using UTL_HTTP and managing all aspects of the call. Details in {message:id=10448611}.
                • 5. Re: How to call a web service in PL/SQL - ORA-24247: network access denied
                  Rooney
                  thanks for the help. ok ... so i downloaded soap, installed it and was able to generate the Envelope. After that I did the following steps:
                  1) finished the function
                  2) ran my webservice
                  3) ran to test my function

                  results:
                  ORA-29273: HTTP request failed
                  ORA-06512: at "SYS.UTL_HTTP", line 1130
                  ORA-24247: network access denied by access control list (ACL)
                  ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 68
                  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. 
                  ran the following statement to test my function:
                  My webservice should return a message such as succes/fail.
                  SELECT CALL_EDQ_WEBSERVICE (33025) AS TEST FROM DUAL;
                  What am i doing wrong ? thanks

                  here is my code:
                  create or replace
                  FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
                  
                  --//URL CALL
                  SOAP_URL CONSTANT VARCHAR2(1000) := 'https://bl11.ussc.gov:7113/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>';
                  
                  --//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
                          --// REPLACE $SENT_ID WITH THE PASSED IN SENT_ID
                       SOAPENVELOPE := REPLACE(SOAP_ENVELOPE, '$SENT_ID', SENT_ID);
                  
                       --// 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 68
                       RAISE;
                  
                  END CALL_EDQ_WEBSERVICE;
                  Edited by: Rooney on Oct 5, 2012 11:12 AM
                  • 6. Re: How to call a web service in PL/SQL - ORA-24247: network access denied
                    Billy~Verreynne
                    Rooney wrote:
                    ORA-29273: HTTP request failed
                    ORA-06512: at "SYS.UTL_HTTP", line 1130
                    ORA-24247: network access denied by access control list (ACL)
                    Oracle 11g has a "firewall" that you need to poke holes in, in order to allow PL/SQL code access to the outside world. Poking holes are done via ACLs - Access Control Lists.

                    I posted some basic sample code for an ACL in {message:id=9579952} for opening the usual http ports and allowing a specific schema external access via the ACL.
                    • 7. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                      Rooney
                      Hi Billy,

                      I created the ACL,
                      I add the user/schema to the ACL,
                      I define the ports/hosts/ips for the ACL, since I am using https on my server I only defined the standard https port .

                      here is the code as well for ACL Part
                      --// create the ACL
                      begin
                           dbms_network_acl_admin.create_acl(
                           acl => 'http-acl.xml',
                           description => 'Web access',
                           principal => 'HTTP_IP_ACCESS',
                           is_grant => TRUE,
                           privilege => 'connect',
                           start_date => null,
                           end_date => null);
                      end;
                       
                      --// add the user/schema to the ACL
                      begin
                           dbms_network_acl_admin.add_privilege(
                           acl => 'power_users.xml',
                           principal => 'USSC_CASES',
                           is_grant => TRUE,
                           privilege  => 'connect',
                           start_date  => null,
                           end_date => null);
                      end;
                       
                      --// define the ports/hosts/ips for the ACL
                      begin 
                           --// standard https port   
                           dbms_network_acl_admin.assign_acl(
                           acl => 'http-acl.xml',
                           host => 'bl11.ussc.gov',
                           lower_port => 7113,
                           upper_port => 7113);
                      end;
                      I am getting this now. what does it mean ?
                      ORA-29273: HTTP request failed
                      ORA-06512: at "SYS.UTL_HTTP", line 1130
                      ORA-29024: Certificate validation failure
                      ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 68
                      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.
                      Thanks for the help

                      Edited by: Rooney on Oct 5, 2012 10:42 PM

                      Edited by: Rooney on Oct 10, 2012 9:54 AM
                      • 8. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                        Billy~Verreynne
                        For HTTPS you need to download the web site's certificate and add it to an Oracle wallet. A wallet (standard web browser feature) contains trusted web server certificates for talking https to a web server.

                        The wallet is created using Oracle's Wallet Manager - executable +$ORACLE_HOME/bin/owm+ - and saved on the server. You then need to use the wallet in your web service call using UTL_HTTP.set_wallet().

                        Have a look at {message:id=1925297}.
                        • 9. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                          Rooney
                          Hi Billy,

                          I talked to my DBA and he said the firewall is disabled on the database. Plus nothing is blocking 1521 port which is the port that talks between EDQ and the database.
                          The port for the application in EDQ is 7113 (https). He also said we don't need to create a wallet.

                          What am i missing here:

                          Here is my code, I really need help on finishing this. Thank you Billy.
                          create or replace
                          FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
                          
                          --//URL CALL
                          SOAP_URL CONSTANT VARCHAR2(1000) := 'https://bl11.ussc.gov:7113/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>';
                          
                          --//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);
                          
                               --// 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;
                               RAISE;
                          
                          END CALL_EDQ_WEBSERVICE;
                          Error
                          Connecting to the database BL27_USSC_CASES.
                          ORA-29273: HTTP request failed
                          ORA-06512: at "SYS.UTL_HTTP", line 1130
                          ORA-29024: Certificate validation failure
                          ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 68
                          ORA-06512: at line 7
                          Process exited.
                          Disconnecting from the database BL27_USSC_CASES.
                          .
                          • 10. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                            JustinCave
                            As Billy said, the error message appears to indicate that you need to install the certificate from the server into a wallet on the database server. If your DBA believes that you don't need to create a wallet, that would imply that your DBA has a different explanation for the error you're getting and a different suggestion for how to resolve it. What is that explanation and suggestion? Have you implemented that suggestion? Did it solve the problem? Assuming that it didn't resolve the problem, we're back to suggesting that you need a wallet.

                            Justin
                            • 11. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                              Rooney
                              ok ... thanks Justin.

                              Can you please provide information/Steps on how to install the certificate from the server into a wallet on the database server ?
                              We have no clue on how to do that.

                              Thank you very much.
                              • 12. Re: How to call a web service in PL/SQL - ORA-29024: Certificate validation
                                JustinCave
                                I think the link Billy provided covers that in some detail. Is there some specific part of that discussion that is unclear to you?

                                Justin
                                • 13. Re: How to call a web service in PL/SQL -failure to open file
                                  Rooney
                                  Hi Justin,

                                  We were able to create the Oracle wallet. However, we are getting this error now,
                                  Connecting to the database BL27_USSC_CASES.
                                  ORA-29273: HTTP request failed
                                  ORA-06512: at "SYS.UTL_HTTP", line 1130
                                  ORA-28759: failure to open file
                                  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 latest code:
                                  create or replace
                                  FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
                                  
                                  --//URL CALL
                                  SOAP_URL CONSTANT VARCHAR2(1000) := 'https://bl11.ussc.gov:7113/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:C:\wallet'; --//'file:/edq_dev_ca1';
                                  --//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, null);
                                    
                                       --// 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;
                                       RAISE;
                                  
                                  END CALL_EDQ_WEBSERVICE;
                                  Thanks.
                                  • 14. Re: How to call a web service in PL/SQL -failure to open file
                                    Billy~Verreynne
                                    The error ORA-28759: failure to open file seems to point to a failure of the UTL_HTTP code to open the wallet file.

                                    The wallet needs to be readable - iow, the Oracle o/s process on the server needs to be able to use standard kernel I/O calls to open and read the wallet.

                                    Wallet files are password protected/encrypted. I have never used a wallet that does not need a password to open successfully - so not specifying a password is not what I expect the correct/robust approach would be.
                                    1 2 Previous Next