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