1 2 Previous Next 27 Replies Latest reply: Jul 16, 2013 11:35 PM by Billy~Verreynne RSS

    Webservice call from PL/SQL

    reddis

      Hi,


       

      I am trying to call a RESTful webservice from a PL/SQL block, I am using a 9i DB.

      Th webservice accepts a single form-field argument, which is XML string input, below is the code where I am trying to make a call.

      I am getting back a very weird error as if the necessary arguments are not being passed, I am not sure if I am passing the parameter in the correct way.

       

           t_request_body := 'request=<?xml version="1.0"?>
      <serviceRequest>
                <auth>
                     <ApplicationID>abc</ApplicationID>
                     <wsPassword>123456</wsPassword>
                </auth>
                <Request>
                     <Processor>BAMS</Processor>
                     <MerchantNumber>AD8360-05</MerchantNumber>
                     <NAME>FirstName</NAME>
                     <Email>TEST@TEST.com</Email>
                     <Address1>1234 SOME Street</Address1>
                     <Address2></Address2>
                     <City>SOME City</City>
                     <State>IN</State>
                     <Zip>12345</Zip>
                     <Country>US</Country>
                     <Phone>123-123-1234</Phone>
                     <Amount>1.00</Amount>
                     <OrderNumber>ORDER123</OrderNumber>
                </Request>
           </serviceRequest>';
      
      url := 'https://RestfulWebserviceCall';
      
      UTL_HTTP.SET_RESPONSE_ERROR_CHECK( TRUE );
      UTL_HTTP.SET_DETAILED_EXCP_SUPPORT( TRUE );
      UTL_HTTP.SET_COOKIE_SUPPORT( TRUE );
      --UTL_HTTP.SET_TRANSFER_TIMEOUT( 30 );
      --UTL_HTTP.SET_FOLLOW_REDIRECT( 3 );
      UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT( TRUE );     
           
      t_http_req:= UTL_HTTP.BEGIN_REQUEST (url=> url, METHOD => 'POST'); 
      UTL_HTTP.SET_HEADER(t_http_req, 'Content-Type', 'text/xml charset=UTF-8');
      UTL_HTTP.SET_HEADER(t_http_req, 'Content-Length', LENGTH(t_request_body));
      UTL_HTTP.WRITE_TEXT(t_http_req, t_request_body);
      t_http_resp:= UTL_HTTP.GET_RESPONSE(t_http_req);
      
      endLoop := FALSE;
      
      LOOP
      EXIT WHEN endLoop;
      BEGIN
      UTL_HTTP.READ_LINE( t_http_resp, buffer, TRUE );
      IF (buffer IS NOT NULL) AND LENGTH(buffer)>0 THEN
           DBMS_OUTPUT.PUT_LINE('Output '||buffer);
      END IF;
      EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN
      endLoop := TRUE;
      END;
      END LOOP;
      
      UTL_HTTP.END_RESPONSE(t_http_resp);
      EXCEPTION
      WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE (t_http_resp);
      END;
      

       

      Thanks for your help in advance.

        • 1. Re: Webservice call from PL/SQL
          rp0428

          And yet there isn't any 'weird error' in anything that you posted.

           

          We cannot comment on that which we cannot see.


          • 2. Re: Webservice call from PL/SQL
            reddis

            Here is part of the attached error message I got back.

             

            coldfusion.runtime.UndefinedElementException: Element REQUEST is undefined in ARGUMENTS.

            coldfusion.runtime.DotResolver.resolveSplitNameInMap(DotResolver.java:109)

            coldfusion.runtime.CfJspPage._resolve(CfJspPage.java:1615)

            coldfusion.runtime.CfJspPage._resolveAndAutoscalarize(CfJspPage.java:1794)

            coldfusion.runtime.CfJspPage._resolveAndAutoscalarize(CfJspPage.java:1787)

            cfUniversalPaymentService2ecfc2130714418$funcAUTHCAPTURE.runFunction(/x01/www/html/webapis_servertest/ecommerce/UniversalPaymentService.cfc:7)

            coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)

            coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47)

            coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)

            coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)

            coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)

            coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

            coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517)

            coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496)

            coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355)

            coldfusion.filter.ComponentFilter.invoke(ComponentFilter.java:187)

            coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:374)

            coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)

            coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)

            coldfusion.filter.PathFilter.invoke(PathFilter.java:94)

            coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)

            coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)

            coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)

            coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)

            coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)

            coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)

            coldfusion.xml.rpc.CFCServlet.invoke(CFCServlet.java:138)

            coldfusion.xml.rpc.CFCServlet.doPost(CFCServlet.java:289)

            javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

            org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)

            javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

            coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)

            jrun.servlet.FilterChain.doFilter(FilterChain.java:86)

            coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)

            coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)

            jrun.servlet.FilterChain.doFilter(FilterChain.java:94)

            jrun.servlet.FilterChain.service(FilterChain.java:101)

            jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)

            jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)

            jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)

            jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)

            jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)

            jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)

            jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

            • 3. Re: Webservice call from PL/SQL
              Billy~Verreynne

              Here is part of the attached error message I got back.

               

              coldfusion.runtime.UndefinedElementException: Element REQUEST is undefined in ARGUMENTS.

               

              There is no Oracle error message, no ORA error code - so why do you think it an Oracle error inside that database stored procedure that makes the web service call? You have not provided any evidence to back that up.

               

              Are there more to the stack trace that shows an Oracle message or error code?

              • 4. Re: Webservice call from PL/SQL
                reddis

                There is no Oracle error as per the folks who maintain the webservice, they are saying I am not passing the arguments. Could you tell if the below way is the correct way to pass XML string (argument text) to the webservice? The webservice accepts a single form-field argument.

                 

                1. UTL_HTTP.SET_HEADER(t_http_req, 'Content-Length', LENGTH(t_request_body)); 
                2. UTL_HTTP.WRITE_TEXT(t_http_req, t_request_body); 

                t_request_body is my XML input string.

                 

                Thanks!

                • 5. Re: Webservice call from PL/SQL
                  Billy~Verreynne

                  Have a look at the example posted in Re: Calling webservice from PLSQL

                  • 6. Re: Webservice call from PL/SQL
                    Sentinel

                    Reddis,

                     

                    One thing to keep in mind when working with XML is that the element names are case sensitive.  In your code element names are in CamelCase or InitCaps for example your Request element is spelled out with an initial capital and the remainder as lower case, however, the message you got back indicated that the

                    Element REQUEST is undefined in ARGUMENTS

                    So it looks like the server is expecting at least the REQUEST element to be spelled out in all upper case.

                     

                    You should review the specifications for the XML payload and ensure that the case of your element names matches those of the specification.

                    • 7. Re: Webservice call from PL/SQL
                      reddis

                      I developed my code in a very similar way, but I am not sure if the whole thing would be applicable to me since I am call a REST webservice in my case.

                       

                      Thanks

                      • 8. Re: Webservice call from PL/SQL
                        reddis

                        It didn't make a difference, I also have to make this webservice call over SSL so i changed the webservice link to a https link.

                        Now I am getting the below error

                         

                        Error ORA-28868: certificate chain check failed

                         

                        Thanks

                        • 9. Re: Webservice call from PL/SQL
                          Billy~Verreynne

                          That means a parent certificate was not found in your Oracle wallet.

                           

                          Simple example. If authority A signs certificate for company B, then you also need certificate A, in addition to B, in the wallet.

                          • 10. Re: Webservice call from PL/SQL
                            reddis

                            Hi Billy,

                             

                            Thanks for your response.

                            I was able to download the certificate issued by authority A for company B, and added as a trusted certificate to the wallet.

                            but I still got the same error message.

                             

                            ORA-28868: certificate chain check failed

                             

                            I also tried the solution mentioned in this thread and still got the same error

                            https://forums.oracle.com/thread/604112

                             

                            Any thoughts?

                             

                            Thanks

                            • 11. Re: Webservice call from PL/SQL
                              reddis

                              Hi,

                               

                              I am getting this error now

                              ORA-29273: HTTP request failed

                              ORA-06512: at "SYS.UTL_HTTP", line 1025

                              ORA-28868: certificate chain check failed

                               

                              Thanks.

                              • 12. Re: Webservice call from PL/SQL
                                Billy~Verreynne

                                As already mentioned:

                                 

                                If the certificate chain failed, it means that the wallet used, does not contain the complete chain of trusted certificates, from the current certificate to the root authority's certificate.

                                 

                                Each certificate that has an Issued By section, needs to have the certificate of that issuer as a trusted certificate. And if that issuer's certificate has been Issued By someone else, then that someone else's certificate is also needed.

                                 

                                Inspect the certificate chain you have loaded, closely - as the error message means that the chain is broken.

                                • 13. Re: Webservice call from PL/SQL
                                  reddis

                                  I happen to delete a couple of trusted certificates that came in by default when I created the wallet since they were expired.

                                  Should I delete all the certificates that came in by default?

                                  The trusted certificate I imported  was issued by Go Daddy Secure Certification Authority.

                                  Is there a way that I can know if the chain is broken?

                                  All I see is the Trusted Certificate is at the bottom of the trusted certificate chain and it says " *.CompanyName.hj-int "

                                   

                                  Thanks

                                  • 14. Re: Webservice call from PL/SQL
                                    Billy~Verreynne

                                    Do not recall expired certificates in an Oracle 11gr2 created wallet... Perhaps using an older Oracle version is part of the problem you are having?

                                     

                                    No - have not seen a way to use Oracle wallet s/w (the GUI owm or CLI orapki utility) supporting a feature showing a broken chain.

                                     

                                    Hmmm... you can use Firefox against the same URL that fails in your UTL_HTTP code. It will download the certificate (and will complain if the auth chain is broken). If this works, use Firefox wallet manager to view the server (root) certificates it has - identify the GoDaddy certificates and copy-and-paste them into your Oracle wallet as trusted certificates.

                                     

                                    Not sure what else to try... Oracle 9i is pretty much a dead and unsupported product. And may not even fully support latest SSL specs?

                                    1 2 Previous Next