This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jul 16, 2013 9:35 PM by BillyVerreynne RSS

Webservice call from PL/SQL

reddis Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

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

  • 6. Re: Webservice call from PL/SQL
    Sentinel Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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

Legend

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