ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

How to use PL/SQL procedures and functions in ODI

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

35 Comments

  1. Hi Cezar.

    I am trying to use an erp database function, but before this I have to establish an environment variable (establish the operating unit ) for the current session , there is a way to achieve this in ODI

    The following sentence It is theenvironment variable I have to establish before the execution of the function

    begin
    fnd_client_info.set_org_context(‘1957′);
    end;

    Thanks in advance

    • Hi,
      You can execute that that PL/SQL block in an ODI procedure.
      Just create a ODI procedure, add an step, choose the Oracle technology, choose the Logical Schema where the command must run, copy the command to the step.

      After that, just add this procedure in a package before other executions…

      Make any sense?

      Cezar

  2. Thanks Cezar. Unfortunately the stored proc cannot be used in a select. It would be so cool if I could simply assign an ODI variable with the out param from the stored proc !

  3. hello Cezar, Is there a way to achieve the same thing in SQL server? I am trying to execute a SQL server stored procedure from ODI. Based on the result in the output param, I then need to decide what action to take. So I created a ODI procedure and then:

    1. Command on Source (SQL Server):
    Declare @Param3 Bit
    Exec hcds.dbo.usp_StageProfile @SessionID = 12345, @DateLastUpdate = ’01/27/14′, @Success = @Param3 Output

    2. Command on Target (Oracle):
    insert into test(createddate, sp_return_code) values (SYSDATE,#Param3);

    This fails with a Caused By: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver]No ResultSet set was produced.
    at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)

    Could you please let me know the right way to do this ?

    • Hi Ed,

      I’m don’t know SQL Serve well… but a question. Is it possible to use that procedure in a “select” command?

  4. Excellent article and really thankyou for sharing. Is there a way within an anonymous block to affect the log counters for updates, inserts, deletes and row count?

  5. Very useful post. It should be a default ODI feature.

    The only downside of it is if you will use within multiple packages that are running in parallel. This could drive to an overlapping situation of the returned value.

  6. hiee,

    can any one give me an step by step understand of a user function i ODI ???

    thanks in advance :)
    vahini

    • Create a new function and provide a Name and Group and syntax .
      Lets take a simple example and create a function CONV_DT_TO_YYYYMM

      so syntax will be CONV_DT_TO_YYYYMM($(variable))
      Now go to Implementation and write the exact syntax

      TO_CHAR($(variable),’YYYYMM’) and select the required Linked Technologies. For
      my example it will be Oracle.

      Now you can call the odi function and instead of $(variable) you can provide the column_name.

      Inshort whereever you want to provide the column name just provide $(variable).

      Hope I have answered your question

  7. Hi Kshitiz,

    Fortunatelly I could solve the issue. It was a problem of privileges that the dba didn’t give to the user I was using. Finally it worked with the following pattern: schema_name.package_name.plsql_procedure_name.

    Anyway, thank you very much for your willingness to help.

    Cheers,

    Hibai.

  8. Hi Cezar and Kshitiz,

    I have a pl/sql package with some procedures. Since I must treat a response, I have tried the third situation explained in this article, but I get an error in ODI which says that the identifier must be declared. I tried both indicating the name of the package as a prefix and without doing that, but non of the ways work. Any idea about which the problem could be?

    Thanks in advance and regards,

    Hibai.

  9. Hi Cezar,

    I’m trying to call a PL/SQL stored procedure from an ODI procedure but when I pass a variable of dataype Text or Alphanumeric in to a VARCHAR2 parameter I get “wrong number or type of arguments”.

    Is there some trick to passing in VARCHAR2 parameters? You don’t say what the datatype of #ODI_var1 is in your first example.

    Cheers

    Craig

    • Craig ,

      I believe Alphanumeric datatype should be fine. Can you please run the procedure in an sql editor and see what values are u passing and if so pass the same way(‘value’ or value ) in the variable and see if that helps. Hope you are running the variable and procedure in a package so that procedure picks the value of variable.

  10. Cezar,

    It was nice chat with you yesterday. The solution you provided was so perfect.

    Thanks alot.

    Regards,
    Harshad Ambekar

  11. Hi cezar,
    Am a first time ODI user, the procedure you explained helped a lot. I have a requirement here am struggling with,
    I need to execute a pl/sql package which takes 4 “in” variables and spits out 3″ out” variables. the interface in the work flow should use the 3 variables. We used this package in other pl/sql programs to use the three out variables in multiple locations inside other programs.
    How can i use this package in ODI(your earlier post is returning only one out variable).

    Thanks in Advance

    Sunny

    • Hi Sunny,

      From the same technique, you can add so many out variables as you wish. In the same proporcion, add ODI variables with the final “selec” (from post) to each one.

      Does it work for you?

  12. thanks a lot………….cezar..it works…

    can u plz give me a link/pdf for all such commands or practices for odi as well as bi publisher/word templates.
    thanks in advance…..

  13. ya two distict odi variable are created….

  14. HI… KSHITIZ
    THANKS FOR d reply..

    another question i hav..

    i hav 2 variables in my pakg.
    variable1 is incremental.i hav to use a case statement in a procedure havin pl/sql code

    begin
    case when var1 =1 then var2= ‘w’
    when var1=2 then var2=’a’
    when var1=3 then var2=’M’
    end

    how to use this..
    my package steps are

    var1–> procedure–>var2.

  15. how can i call a single plsql proceure from a package more that once.i want on running a package having a procedure ,package shud execute the procedure 10 times…in odi

    • Look into this post – http://odiexperts.com/?p=531
      and replace the odistartscen with ODI procedure through which you can call the pl/sql procedure.

      or try this simple idea using a single variable
      1. Create a new Varaible name it V_LOOP and with ‘ Numeric ‘ and ‘ historize ‘
      2. Drag into the package and assign 1
      3. Next step your procedure
      4.Evaluate V_LOOP =10 ( true end , false continue )
      5. Increment V_LOOP
      6. Join to procedure.

      I have send you an email too at your tcs id.

      Please feel free if you have any other question

  16. Hi Jay,

    Yes, you can handle multiple out parameters by creating multiples functions.

    About the variable that happens because isn’t possbile to set a value from a PL/SQL into an ODI variable directly, then I did that thru the select… from dual into an ODI variablr refreshing tab.

    Make any sense?

    Cezar

  17. Very useful article – works like magic. Especially Scenario #3

    Is it possible to handle multiple OUT parameters from Oracle Stored Proc in the way ?

    I am still wondering how it really works under the hood. The Variable in the Temp Package is set in the Autonomous Stored Proc and the same is retreived by calling the Function from Dual. Whats goes on here ?

  18. Greetings article has very much assisted, Excellent work

  19. Hi Cezar,

    I’m trying to call a SQL function from within an ODI interface which does some advanced data cleansing on a description filed. The functions contains a loop, some If, Then… Else statements and variables and returns the cleansed description.
    e.g.

    SELECT Parent, Member, CleanData( Alias )
    FROM DimCust

    Function dbo.CleanData(
    @Text AS NVARCHAR(80)
    )
    RETURNS NVARCHAR(80) AS
    BEGIN

    Declare @Index AS INT
    Declare @RetVal AS NVARCHAR(80)

    Set @Index = 1

    While @Index < Len(@Text)
    Begin
    If Left(@Text, 1) = '+'
    Begin
    Set @RetVal = 'SomeTranslations'
    End
    @Index = @Index + 1
    End

    RETURN @RetVal

    End

    How would this be done in ODI?

    Thanks for replying,
    Rudy

  20. Hi Cezar,

    I’m trying to call a funtction from within an ODI interface, which does some advanced data cleansing on a description field before writing it to a target table. E.g.
    SELECT Parent, Member, DataCleans( Alias )
    FROM DimCust

    The function contains some if statements and returns the cleansed Alias.

    How would this be done in ODI??

    Thanks for you reply!!

    Rgds,
    Rudy

    • Hi Rudy,
      In the way that you show, seems that you will map any column from query ( Parent, Member, DataCleans( Alias )) to a column in target and, of course, your source table will be DimCust.

      All you need to do in this case, is map the DataCleans( Alias ) in the target column directly. No additional work is necessary.

      Is that what you need? If not, send me an email and I will try to help you…

      Thank you very much for come to visit us!

      Cezar Santos

  21. I will be waiting for your post.

    Thanks,
    Sam

  22. Hi Sam,

    I will write a post about it…

    If you have an urgent requirement, send me a email (Contact link at top of this page…)

  23. Ceaser,
    Where do we need to enter the code, ‘command on source’ or ‘command on target’?

    Can you explain me what is the need of ‘command on source’ and ‘Command on target’ while creating a procedure?

    Thanks in advance,
    Sam.

  24. Hi Cezar,
    The procedure works correctly, thanks so much!!

  25. Hi Atl,

    Sorry, was a little mistake at the last command. Change it to:

    select P$_temp.get_ret
    from dual

    I already changed it at post…

    Cezar

  26. hi,
    I found very useful post, but using the scenario 3, I encountered difficulties in assigning the value of ‘OUT’ to the variable, which is valued correctly in step 3. I Add the procedures followed for the ODI variable (in Refresh mode), but it returns the following error when trying to bind the value to the ODI variable:

    6553 : 65000 : java.sql.SQLException: ORA-06553: PLS-221: ‘PV_RET’ is not a procedure or is undefined

    java.sql.SQLException: ORA-06553: PLS-221: ‘PV_RET’ is not a procedure or is undefined

    can you help me?
    thanks for all

  27. very useful stuff. Particularly scenario 3

Leave a Reply

Required fields are marked *.