ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

How to call a Web Service that start an ODI Scenario and waits its complete execution

Hi friends,

I just created this technique because I was in a POC (Prove of Concept) and needed to call a WS (Web Service) but the WS must wait the scenario execution until the end and, too, receives a return (OK or Not OK).

As is from common knowledge , calls the ODI web service for scenario execution only starts a scenario but doesn’t “wait” the scenario execution. The response just indicates that the process was started, not if it finish with or without error.

This behavior is absolutely normal once a scenario could take hours to finish and WS aren’t done for that.

However, as a programmer, it ‘s always good to break the rules!

What I’m about to show is very simple and easy. The technique was designed to work with Oracle.

Recipe:

Ingredients
 
1 ODI scenario (from any developed process)
1 Oracle Database with the following packages compiled :
 http://www.oracle.com/technology/tech/java/jsp/pdf/calling_shell_commands_from_plsql_1.1.pdf
1 “Temporary” table created just for be called for a WS . This table should have the same number of columns as the ODI scenario parameters or at least one column of any datatype if the scenario doesn’t receive parameters.
1 Trigger in the temporary table

 

How to do:
1º) Publish a web service to insert data into this temporary table (Called  Temp_Table from now forward)
You can do that from ODI using a SKM (Service knowledge Module)
2º) Be sure about to have followed all instructions from Oracle document (link at ingredients)
3º) Create a trigger on Temp_Table like:

 

create or replace TRIGGER Staging_Area.TG_WS
BEFORE INSERT ON ODISA.TEMP_TABLE
FOR EACH ROW
DECLARE
  
   v_Ret varchar2(500);
BEGIN
      v_Ret := os_command.exec(‘startscen ANY_SCENARIO 001 MY_CONTEXT -v=3 -NAME=Agent_Windows MY_PROJECT.vParameter1=’||:new.MY_TEMP_TABLE_COLUMN1 ||’ MY_PROJECT.vParameter2=’||:new.MY_TEMP_TABLE_COLUMN2;
 
      if v_Ret<> ‘0’ then  /* zero is the return code for OK */
         RAISE_APPLICATION_ERROR(‘-20001’, ‘ ODI Error : ‘||v_Ret, FALSE);
      end if;
      
END;
4º )  MIX EVERYTHING and call the WS.
The PL/SQL code at trigger will execute the scenario and waits for a response if the ODI scenario finish with or without error.

Well my friends, here we have a way to start a scenario from WS and got a OK or KO return. Keep in mind that it is a trick to add a characteristic doesn’t present at ODI in the current version.

See you around!

Cezar Santos

7 Comments

  1. /*
    Xml to invoke odi scenario from soap ui
    —————————————————

    username
    password
    WREP

    INT_EXAMPLE
    001
    Development
    true
    5

    Output
    ———-




    S:Server
    ODI-1274: Agent Exception
    Caused by: oracle.odi.core.security.BadCredentialsException: ODI-10199: Incorrect ODI username or password.
    [Security:090304]Authentication Failed: User FUSION_APPS_HCM_ODI_SUPERVISOR_APPID javax.security.auth.login.FailedLoginException: [Security:090302]Authentication Failed: User FUSION_APPS_HCM_ODI_SUPERVISOR_APPID denied

    */

  2. Hi cezar santos,
    While invoking an odi scenario from a web service (soap ui) , i get an authentication error.

    Xml to invoke odi scenario from soap ui
    —————————————————

    username
    password
    WREP

    INT_EXAMPLE
    001
    Development
    true
    5

    Output
    ———-




    S:Server
    ODI-1274: Agent Exception
    Caused by: oracle.odi.core.security.BadCredentialsException: ODI-10199: Incorrect ODI username or password.
    [Security:090304]Authentication Failed: User FUSION_APPS_HCM_ODI_SUPERVISOR_APPID javax.security.auth.login.FailedLoginException: [Security:090302]Authentication Failed: User FUSION_APPS_HCM_ODI_SUPERVISOR_APPID denied

    Thanks,
    Vinodh

  3. Hi,
    I tried to use OdiInvokeWebservice in ODI 12c to call MSMDPUMP.DLL service
    which allows me to connect to SSAS Multidimensional.

    I have a WSDL file in local PC : file:///F:\USERS\Sona\SSAS\xmla_1.wsdl
    and Request File is : F:/USERS/Sona/SSAS/request.xml
    Port type :MsXmlAnalysisSoap
    Operation:Execute

    Logical Agent: LOCAL

    MY WSDL File:

    I have this error:

    ODI-1226: Step Test XML fails after 1 attempt(s).
    ODI-1241: Oracle Data Integrator tool execution fails.
    Caused By: com.sunopsis.wsinvocation.SnpsWSInvocationException: java.lang.NoClassDefFoundError: Could not initialize class weblogic.wsee.security.configuration.MBeanHelper
    at oracle.odi.wsinvocation.client.impl.jaxws.OdiJaxwsParserImpl.createMessage(OdiJaxwsParserImpl.java:361)
    at com.sunopsis.wsinvocation.client.impl.AbstractJWSDLParserImpl.getWebServiceMessage(AbstractJWSDLParserImpl.java:285)
    at com.sunopsis.wsinvocation.client.impl.AbstractJWSDLParserImpl.getWebServiceRequestByOperation(AbstractJWSDLParserImpl.java:297)
    at com.sunopsis.dwg.tools.common.WebserviceUtils.invoke(WebserviceUtils.java:99)
    at com.sunopsis.dwg.tools.InvokeWebService.actionExecute(InvokeWebService.java:327)
    at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:296)
    at oracle.odi.runtime.agent.execution.SessionTask.execIntegratedFunction(SessionTask.java:942)
    at oracle.odi.runtime.agent.execution.SessionTask.executeOdiCommand(SessionTask.java:575)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
    at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:20)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:51)
    at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:214)
    at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:135)
    at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:856)
    at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2004)
    at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:544)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:709)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:624)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
    at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:789)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:330)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$2700(StartSessRequestProcessor.java:80)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:957)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:177)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:110)
    at java.lang.Thread.run(Thread.java:724)
    Caused by: java.lang.NoClassDefFoundError: Could not initialize class weblogic.wsee.security.configuration.MBeanHelper
    at weblogic.wsee.security.configuration.WssConfiguration.(WssConfiguration.java:107)
    at weblogic.wsee.security.policy.WssPolicyContext.init(WssPolicyContext.java:58)
    at weblogic.wsee.security.policy.WssPolicyContext.(WssPolicyContext.java:45)
    at weblogic.wsee.jaxws.framework.jaxrpc.client.ClientEnvironmentFactory.initPolicyContext(ClientEnvironmentFactory.java:66)
    at weblogic.wsee.jaxws.framework.jaxrpc.EnvironmentFactory.getService(EnvironmentFactory.java:1233)
    at weblogic.wsee.jaxws.WLSTubelineAssemblerFactory$TubelineAssemblerImpl.createClient(WLSTubelineAssemblerFactory.java:93)
    at com.sun.xml.ws.client.Stub.createPipeline(Stub.java:344)
    at com.sun.xml.ws.client.Stub.(Stub.java:311)
    at com.sun.xml.ws.client.Stub.(Stub.java:244)
    at com.sun.xml.ws.client.dispatch.DispatchImpl.(DispatchImpl.java:159)
    at com.sun.xml.ws.client.dispatch.DispatchImpl.(DispatchImpl.java:148)
    at com.sun.xml.ws.client.dispatch.DispatchImpl.(DispatchImpl.java:138)
    at com.sun.xml.ws.client.dispatch.SOAPMessageDispatch.(SOAPMessageDispatch.java:82)
    at com.sun.xml.ws.api.pipe.Stubs.createSAAJDispatch(Stubs.java:149)
    at com.sun.xml.ws.api.pipe.Stubs.createDispatch(Stubs.java:273)
    at com.sun.xml.ws.client.WSServiceDelegate.createDispatch(WSServiceDelegate.java:515)
    at weblogic.wsee.jaxws.spi.WLSProvider$ServiceDelegateImpl.internalCreateDispatch(WLSProvider.java:978)
    at weblogic.wsee.jaxws.spi.WLSProvider$ServiceDelegateImpl$DispatchClientInstanceFactory.createClientInstance(WLSProvider.java:1231)
    at weblogic.wsee.jaxws.spi.ClientInstancePool.takeSimpleClientInstance(ClientInstancePool.java:388)
    at weblogic.wsee.jaxws.spi.ClientInstancePool.take(ClientInstancePool.java:243)
    at weblogic.wsee.jaxws.spi.WLSProvider$ServiceDelegateImpl.createDispatch(WLSProvider.java:888)
    at weblogic.wsee.jaxws.spi.WLSProvider$ServiceDelegateImpl.createDispatch(WLSProvider.java:851)
    at com.sun.xml.ws.client.WSServiceDelegate.createDispatch(WSServiceDelegate.java:542)
    at com.sun.xml.ws.client.WSServiceDelegate.createDispatch(WSServiceDelegate.java:521)
    at com.sun.xml.ws.client.WSServiceDelegate.createDispatch(WSServiceDelegate.java:489)
    at javax.xml.ws.Service.createDispatch(Service.java:352)
    at oracle.odi.wsinvocation.client.impl.jaxws.OdiJaxwsParserImpl.createMessage(OdiJaxwsParserImpl.java:336)
    … 25 more

    Can you plese help me on this ?
    Regards,
    Sona

    • Hi,

      it’s some kind of rights missing on ODI to Weblogic… I believe you’ll need to open an SR at Oracle….

      • Thank You for your reply
        Sorry, I should mention to I’m new in ODI!
        I do not understand what you mean. how can I open an SR at oracle?
        thank u

  4. this PDF is not opening : http://www.oracle.com/technology/tech/java/jsp/pdf/calling_shell_commands_from_plsql_1.1.pdf

    please mail me the procedure of calling web services using SKM and OBIEE.

    • Hi,

      you can call webservices thru ODI tool InvokeWebServices.

      SKM are used to create Web Services, not for call them.

      Unfortunately I don’t have such procedure for OBIEE.

      Thanks for visit us!

Leave a Reply

Required fields are marked *.