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.


1 ODI scenario (from any developed process)
1 Oracle Database with the following packages compiled :
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
   v_Ret varchar2(500);
      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;
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


Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.