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

9 Comments

  1. Hi,

    I have requirement that whenever we place a file (any file like CSV, txt or XML) in a folder, ODI should pick and process the file.

    Usually what we do is create package and generate scenario for that package and schedule it for like every 10 minutes. While executing scenario if any file is present in folder, it will pick and process the file. But if file is not present in the folder still our scenario will execute. It should not happen.

    How can i do that??

    Please do needful.

    Thanks & Regards,
    Shiva Kumar

  2. /*
    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

    */

  3. 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

  4. 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

  5. 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 *.