Hi people,

It’s very frequent I got emails asking me how to use PL/SQL function and procedures in ODI.

For that, all you need is an Anonymous blocks of PL/SQL. That is all.

A single example is:

1 – Situation:

You need to call a generic ERP PL/SQL procedure that has 3 “in” parameters before starts an interface

Procedure description:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 in date)

Solution:

1. Create a ODI Procedure and one step inside  
2. Choose the technology as Oracle
3. Choose the right Logical Schema that reach the procedure
4. Write the following code:

Begin

MY_PLSQL_PROC(#ODI_var1, #ODI_var2, sysdate);

end;

Voila!!! It’s possible to call a procedure!

And yes, I know that I showed the simplest possible case… hehehehehe!

Ok, let us go to something a little more complex:

 

2 – Situation:

You need to call a generic ERP PL/SQL procedure that has 2 “in” parameters and 1 “out” parameter before starts an interface. This “out” parameter brings if there is a business error.

Procedure description:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 out varchar2)

 Solution:

1. Create a ODI Procedure and one step inside
2. Choose the technology as Oracle
3. Choose the right Logical Schema that reach the procedure
4. Write the following code:

Declare

v_ret varchar2(1000);

Begin

MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);

If v_ret is not null then

raise_application_error(-20001, ‘The following error is raised: ‘ || v_ret);

end if;

end;

This code will allow you get a business error into Operator and even use the getPrevStepLog API to send an email with the error description.

Ok, can we add more complexity???

 

3 – Situation:

You need to call a generic ERP PL/SQL procedure that has 2 “in” parameters and 1 “out” parameter before starts an interface. This “out” needs to be passed to an ODI variable to future uses.

Procedure description:

MY_PLSQL_PROC(par1 in varchar2, par2 in number, par3 out varchar2)

Solution:

1. Create a ODI Procedure and 3 steps inside

• First Step
  1. Choose the technology as Oracle
  2. Choose the right Logical Schema that reach the procedure
  3. Write the following code:

create or replace package P$_temp as

pv_ret VARCHAR2(1000);

function get_ret return varchar2;

end P$_temp;

 

• Second Step
  1. Choose the technology as Oracle
  2. Choose the right Logical Schema that reach the procedure
  3. Write the following code:

create or replace package body P$_temp as

function get_ret return varchar2 as

begin
return pv_ret;
end get_ret;

end P$_temp;

 

• Third Step
  1. Choose the technology as Oracle
  2. Choose the right Logical Schema that reach the procedure
  3. Write the following code:

Declare

v_ret varchar2(1000);

Begin

MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);

P$_temp.pv_ret := v_ret;

end;

Now, for recover this value into a ODI variable is so simple as create the ODI variable with the following code into Refreshing tab: (use the same Logical schema as used in the procedure)

select P$_temp.get_ret

from dual

Add the procedure followed for the ODI variable (in Refresh mode) and you will have the ODI variable composed with the “OUT” value from a PL/SQL Procedure.

 

Well my friends, that is all. At true, it isn’t so complicated.

Any comment is highly appreciated!

Best Regards to all,

Cezar Santos