Tuesday, August 16, 2011

Creating a PL/SQL based webservice with a XMLType in/out parameter (EBS 12.1.1)

Out of the box can we not create a webservice that have a parameter (in or out) of type XMLType using SOA Gateway. When the service is called you will get an error.

In-parameter:
Error Description:  Error occured while service was being generated. 
Error Details:  oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime.

    Missing class: oracle.xdb.XMLType

  Dependent class: oracle.tip.adapter.db.sp.oracle.TypeConverter
           Loader: AppsSOAAdapter:0.0.0
      Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter/DBAdapter.jar
    Configuration:  in /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter

The missing class is available from the following locations:

  1. Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/rdbms/jlib/xdb.jar (from  in /u02/oracle/VISION2/apps/apps_st/comn/webapps/oafm/)
     This code-source is available in loader oafm.root:0.0.0. 

  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.invoke(JCAHandler.java:222)
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.handleRequest(JCAHandler.java:153)
  at oracle.apps.fnd.soa.provider.SOAProvider.processMessage(SOAProvider.java:295)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doEndpointProcessing(ProviderProcessor.java:956)
  at oracle.j2ee.ws.server.WebServiceProcessor$1.run(WebServiceProcessor.java:358)
  at java.security.AccessController.doPrivileged(Native Method)
  at javax.security.auth.Subject.doAs(Subject.java:396)
  at oracle.j2ee.ws.server.WebServiceProcessor.invokeEndpointImplementation(WebServiceProcessor.java:355)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doRequestProcessing(ProviderProcessor.java:466)
  at oracle.j2ee.ws.server.WebServiceProcessor.processRequest(WebServiceProcessor.java:114)
  at oracle.j2ee.ws.server.WebServiceProcessor.doService(WebServiceProcessor.java:96)
  at oracle.j2ee.ws.server.WebServiceServlet.doPost(WebServiceServlet.java:194)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
  at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
  at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
  at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
  at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
  at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
  at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
  at java.lang.Thread.run(Thread.java:619)
oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime.

    Missing class: oracle.xdb.XMLType

  Dependent class: oracle.tip.adapter.db.sp.oracle.TypeConverter
           Loader: AppsSOAAdapter:0.0.0
      Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter/DBAdapter.jar
    Configuration:  in /u02/oracle/VISION2/apps/tech_st/10.1.3/j2ee/oafm/connectors/AppsSOAAdapter/AppsSOAAdapter

The missing class is available from the following locations:

  1. Code-Source: /u02/oracle/VISION2/apps/tech_st/10.1.3/rdbms/jlib/xdb.jar (from  in /u02/oracle/VISION2/apps/apps_st/comn/webapps/oafm/)
     This code-source is available in loader oafm.root:0.0.0. 

  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.invoke(JCAHandler.java:222)
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.handleRequest(JCAHandler.java:153)
  at oracle.apps.fnd.soa.provider.SOAProvider.processMessage(SOAProvider.java:295)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doEndpointProcessing(ProviderProcessor.java:956)
  at oracle.j2ee.ws.server.WebServiceProcessor$1.run(WebServiceProcessor.java:358)
  at java.security.AccessController.doPrivileged(Native Metho

Out-parameter:
Error Description  Error occured while service was being generated. 
Error Details  oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime.oracle/classloader/util/AnnotatedNoClassDefFoundError
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.invoke(JCAHandler.java:222)
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.handleRequest(JCAHandler.java:153)
  at oracle.apps.fnd.soa.provider.SOAProvider.processMessage(SOAProvider.java:295)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doEndpointProcessing(ProviderProcessor.java:956)
  at oracle.j2ee.ws.server.WebServiceProcessor$1.run(WebServiceProcessor.java:358)
  at java.security.AccessController.doPrivileged(Native Method)
  at javax.security.auth.Subject.doAs(Subject.java:396)
  at oracle.j2ee.ws.server.WebServiceProcessor.invokeEndpointImplementation(WebServiceProcessor.java:355)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doRequestProcessing(ProviderProcessor.java:466)
  at oracle.j2ee.ws.server.WebServiceProcessor.processRequest(WebServiceProcessor.java:114)
  at oracle.j2ee.ws.server.WebServiceProcessor.doService(WebServiceProcessor.java:96)
  at oracle.j2ee.ws.server.WebServiceServlet.doPost(WebServiceServlet.java:194)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
  at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
  at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
  at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
  at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
  at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
  at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
  at java.lang.Thread.run(Thread.java:619)
oracle.apps.fnd.soa.util.SOAException: ServiceGenerationError: Exception returned from JCA Service Runtime.oracle/classloader/util/AnnotatedNoClassDefFoundError
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.invoke(JCAHandler.java:222)
  at oracle.apps.fnd.soa.provider.services.jca.JCAHandler.handleRequest(JCAHandler.java:153)
  at oracle.apps.fnd.soa.provider.SOAProvider.processMessage(SOAProvider.java:295)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doEndpointProcessing(ProviderProcessor.java:956)
  at oracle.j2ee.ws.server.WebServiceProcessor$1.run(WebServiceProcessor.java:358)
  at java.security.AccessController.doPrivileged(Native Method)
  at javax.security.auth.Subject.doAs(Subject.java:396)
  at oracle.j2ee.ws.server.WebServiceProcessor.invokeEndpointImplementation(WebServiceProcessor.java:355)
  at oracle.j2ee.ws.server.provider.ProviderProcessor.doRequestProcessing(ProviderProcessor.java:466)
  at oracle.j2ee.ws.server.WebServiceProcessor.processRequest(WebServiceProcessor.java:114)
  at oracle.j2ee.ws.server.WebServiceProcessor.doService(WebServiceProcessor.java:96)
  at oracle.j2ee.ws.server.WebServiceServlet.doPost(WebServiceServlet.java:194)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
  at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
  at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
  at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
  at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
  at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
  at oracle.oc4j.network.ServerSo

To fix this we need to include xdb.jar in the oc4j.jar.
  1. Shutdown OC4J ($ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall)
  2. Go to $ORACLE_HOME/j2ee/home (make sure that you are in the 10.1.3 folder, NOT 10.1.2)
  3. Make a backup of file oc4j.jar
  4. Copy file oc4j.jar to a temp folder
  5. Extract jar file
    jar xf oc4j.jar
  6. Edit file META-INF/boot.xml
    - In the shared library section called oracle.xml (<shared-library name="oracle.xml" parent="api" version="10.1.0_2">) look for the line <code-source path="${oracle.home}/rdbms/jlib/xdb.jar"/>
    - If the line is missing, then add the line to the section
    - Double check that xdb.jar exists in $ORACLE_HOME/rdbms/jlib/ (10.1.3 folder)
  7. Re-packaged boot.xml into oc4j.jar with path meta-inf
    jar uf oc4j.jar META-INF/boot.xml
  8. Copy the new oc4j.jar to $ORACLE_HOME/j2ee/home (10.1.3 folder)
  9. Restart OC4J ($ADMIN_SCRIPTS_HOME/adopmnctl.sh startall)

13 comments:

  1. We are in 10.1.2 "/u01/apps/tech_st/10.1.2". I am trying XMLTYPE in and OUT variables in plsql procedure. Seeing message in SOA monitor log
    ---------
    "55485" "10-Aug-2012 17:26:44" "oracle.apps.fnd.soa.provider.services.jca.JCAHandler.invoke" "Exception" "Exception returned from JCA Service Runtime.java.lang.reflect.InvocationTargetException"
    -----------

    Any idea why it is not taking XMLTYPE input.
    I am able to generate and deploy WSDL successfully.

    ReplyDelete
  2. We got it. We have it named as IAS_ORACLE_HOME. Your steps worked perfectly. Thanks a lot.

    ReplyDelete
  3. It worked!! Thank you my friend!!! You just saved my life... :D

    ReplyDelete
  4. Hi Daniel

    I am getting

    "Unexpected exception caught: javax.xml.ws.soap.SOAPFaultException, msg=FND_SOA_SERVICE_EXECUTION_ERR:oracle.apps.fnd.soa.util.SOAException: ServiceExecutionError: Error while executing the service Exception returned from JCA Service Runtime. Exception returned from JCA Service Runtime. null
    :Please see service monitor logs for full error trace"

    When calling a HR_PERSON_API through SOA Gateway of EBS. The difference is that mu exception is null. DO you think this is related?

    Regards

    ReplyDelete
    Replies
    1. Hi,

      I have not seen this one but I guessing it something to do with how you call the API. Are 100% sure that all parameters are there?

      Br Daniel

      Delete
  5. Hi,

    Any idea if it is possible to deploy custom webservices in the OC4J for R12.1.3?

    Thanks & regards,
    Gaurav

    ReplyDelete
    Replies
    1. I guess it would work in theory but I have not done it and not heard of anybody else doing it. When people have custom webservices they have this deployed on separate servers which I would recommend.

      Regards,
      Daniel

      Delete
  6. Hi Gaurav,

    It's very much possible, we can expose any PLSQL package using ISG in R12.1.3

    Regards
    Apurba k Saha

    ReplyDelete
  7. Hi Daniel,

    I am trying a PL/SQL Webservice with XMLTYPE as OUT parameter. and I am getting this error -
    oracle.apps.fnd.soa.util.SOAException: ServiceExecutionError: Error while executing the service Exception returned from JCA Service Runtime. Exception returned from JCA Service Runtime. null at
    oracle.apps.fnd.soa.provider.services.jca.JCAHandler.handleRequest(JCAHandler.java:135) at

    Any ideas why ?

    Also, in the oc4j.jar I dont see boot.xml on our version ..

    ReplyDelete
    Replies
    1. Instructions are based on 12.1.1. If you are not running that version there is a possibility that it does not fit.

      Delete
  8. Hi Chinappa,

    I am facing the same issue. Did you find any solution to this??

    Thanks and Regards,
    Rati

    ReplyDelete

javascript:void(0)