Warning: Illegal string offset 'ssb_og_tags' in /home/content/10/4632510/html/wp-content/plugins/simple-social-buttons/simple-social-buttons.php on line 1466


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)


  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:


MY_PLSQL_PROC(#ODI_var1, #ODI_var2, sysdate);


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)


  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:


v_ret varchar2(1000);


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;


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)


  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

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:


v_ret varchar2(1000);


MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);

P$_temp.pv_ret := v_ret;


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


  1. if we upgrade from odi 11g to 12c then interface running fine mean temporary and other or need to update manually?

  2. Hi Cezar, Thank you very much for the 3rd scenario. It worked with no problems at all, after nearly 8 years of your post. ODI Studio 12c + Oracle Database12c. Great Help.

  3. i always get PLS-00357: Table,View Or Sequence reference ” not allowed in this context when passing project variables in scripts,
    could you help ?

  4. Hi,
    With ODI 12 you can use this way to get out parameter in ODI variable :

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

    import java.sql.*;

    // open a connection whith target logical schema (use "SRC" for the source logical schema)
    Connection conn=odiRef.getJDBCConnection("DEST");

    // define PL/SQL call
    CallableStatement pls = conn.prepareCall("begin .MY_PLSQL_PROC(?, ?, ?); end;”);

    // define I/O parameter
    pls.setString(1, “#ODI_var1”);
    pls.setInt(2, “#ODI_var1″);
    pls.registerOutParameter(3, Types.VARCHAR);

    // execute PL/SQL with parameter

    // get out parameter
    String ls_paramOut = pls.getString(3);

    // close connection

    Second Step

    Use the following refresh command for your ODI variable

    select ” from dual

  5. Hi,
    I have to run a procedure as given below in the Odi 11g,when i wrote this procedure as it is in the odi procedure and ran the script its working without giving any error,but the data is not updating to the particulate table depending on this procedure.

  6. Hi,

    I have a informix stored procedure that needs to be calld and the result set has to insert into the postgres table;
    I am follwing steps in ODI procedures to call the informix procedure. It completes but it keeps returning 0 rows.

    Command on Target:
    insert into iss_skavuluri.cisco_test(test1_startdate,test2_enddate,test3,test4,test5,test6,test7)

    Command On Source:
    call sp_agent_state_detail(‘2015-10-28 04:00:00′,’2015-11-03 04:59:59′,’0′,null,null,null,’Grad Advising,UnGrad Advising,MIL VET STU Advising’)

    Can you please help me with the solution.


    • Hi,

      You need a select command at source that return records… ODI doesn’t understand the return of a call command.

      • Hi,

        But can you please let me know how can i select the procedure from informix.


        • Hi,

          Actually what i meant was, how can i call the procedure to retrieve the data in this scenario. Can you please help.


          • Hi,
            I am following code to bring the data from informix procedure to PostgreSQL table. I am not able to debug where exactly the null pointer error is.

            command on target:
            insert into iss_skavuluri.cisco_test
            agent_name ,
            :agent_name ,

            command on source:

            Now i am getting the following error:

            at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:85)
            at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
            at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
            at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
            at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
            at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
            at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
            at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
            at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
            at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
            at java.lang.Thread.run(Thread.java:745)

            I am not able to find the error. Can you please help me in debugging the error.


          • i forgot to add command on source:

          • command on source:

  7. Does ODI not allow the direct assignment via in output parameter in a stored procedure? In other words, the oracle stored procedure MY_PLSQL_PROC, #ODI_var3 is defined as an output parameter in stored procedure which is assigned to the odi variable ODI_var3. This way ODI_var3 can later be used in an interface/mapping.


    MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);

    • Unfortunately not… ODI doesn’t share the memory area for variable to get them “outputed” by a stored procedure…

  8. Dear Cezar,
    Nice Blog. very useful stuff.
    i’m newbie to odi. Using ODI 12c( i have requirement like this,
    1. I have a stored procedure which will do some join operations between my source tables and give us result set.
    2. Now i need to invoke that stored procedure and capture that result set.
    3. I need to insert that result set into target table.

    Please guide me with solution for above requirement.

    Thank You.

    Dinesh Konuri.

  9. Hello Cezar,

    Very nice blog … I need your help, I have a requirement, where in source I have a CSV file with multiple records in it. At the target side i have a stored procedure having 3 In parameters out of which one is of BLOB type and two Out parameters. I have two pass each record of the csv file to Stored Procedure ans stored procedure will perform the next set of actions. Stored procedure might can send the error message as well which i have to catch and send it to the caller. Could you please guide me for the same, how to proceed.


  10. 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


    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?


  11. 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 !

  12. 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?

      • Hy Cezar, Hi Bob. The error occurs just because ODI expects a set of data as result.

        I would try the following solution:

        after your exec statement include the

        SELECT @Param3


        So, your code will return a result set.

  13. 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?

  14. 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.

  15. hiee,

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

    thanks in advance 🙂

    • 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

  16. 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.



  17. 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,


  18. 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.



    • 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.

  19. Cezar,

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

    Thanks alot.

    Harshad Ambekar

  20. 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


    • 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?

  21. 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…..

  22. ya two distict odi variable are created….

    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

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

    how to use this..
    my package steps are

    var1–> procedure–>var2.

  24. 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

  25. 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?


  26. 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 ?

  27. Greetings article has very much assisted, Excellent work

  28. 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.

    SELECT Parent, Member, CleanData( Alias )
    FROM DimCust

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

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

    Set @Index = 1

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

    RETURN @RetVal


    How would this be done in ODI?

    Thanks for replying,

  29. 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!!


    • 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

  30. I will be waiting for your post.


  31. 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…)

  32. 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,

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

  34. 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…


  35. 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

  36. very useful stuff. Particularly scenario 3

Leave a Reply

Required fields are marked *.

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