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
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; 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!