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 tableHow 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
February 17, 2017 at 5:22 PM
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
February 17, 2017 at 5:29 PM
Hi,
Just use the ODI Tool waitforfile, it only process the next step if found a file.
Make any sense?
December 18, 2015 at 11:02 AM
/*
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
*/
December 18, 2015 at 11:01 AM
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
August 12, 2014 at 11:46 AM
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
August 12, 2014 at 11:56 AM
Hi,
it’s some kind of rights missing on ODI to Weblogic… I believe you’ll need to open an SR at Oracle….
August 12, 2014 at 12:37 PM
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
January 28, 2014 at 4:44 AM
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.
January 28, 2014 at 5:20 AM
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!