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:
- Create a ODI Procedure and one step inside
- Choose the technology as Oracle
- Choose the right Logical Schema that reach the procedure
- 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:
- Create a ODI Procedure and one step inside
- Choose the technology as Oracle
- Choose the right Logical Schema that reach the procedure
- 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:
- Create a ODI Procedure and 3 steps inside
- First Step
- Choose the technology as Oracle
- Choose the right Logical Schema that reach the procedure
- 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
- Choose the technology as Oracle
- Choose the right Logical Schema that reach the procedure
- 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
- Choose the technology as Oracle
- Choose the right Logical Schema that reach the procedure
- 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
July 7, 2017 at 1:00 AM
if we upgrade from odi 11g to 12c then interface running fine mean temporary and other or need to update manually?
June 12, 2017 at 3:08 PM
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.
June 12, 2017 at 5:49 PM
Thank you Ulger, It’s good to know it helps you!
January 8, 2017 at 4:29 AM
i always get PLS-00357: Table,View Or Sequence reference ” not allowed in this context when passing project variables in scripts,
could you help ?
December 21, 2016 at 5:10 PM
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
pls.executeUpdate();
// get out parameter
String ls_paramOut = pls.getString(3);
// close connection
conn.close();
@>
Second Step
Use the following refresh command for your ODI variable
select ” from dual
August 3, 2016 at 5:35 AM
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.
declare
begin
pkg_emp_punctuality.PR_EMP_PUNCTUALITY;
end;
December 11, 2015 at 6:20 PM
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)
values
(
:start_time,
:end_time,
:param1,
:param2,
:param3,
:param4,
:agent_group
)
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.
Thanks,
Subbu.
December 11, 2015 at 9:29 PM
Hi,
You need a select command at source that return records… ODI doesn’t understand the return of a call command.
December 13, 2015 at 6:08 PM
Hi,
But can you please let me know how can i select the procedure from informix.
Thanks,
Subbu.
December 14, 2015 at 4:48 PM
Hi,
Actually what i meant was, how can i call the procedure to retrieve the data in this scenario. Can you please help.
Thanks,
Subbu.
December 14, 2015 at 7:57 PM
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_log_id,
agent_extension,
Transition_Time,
Agent_State,
Reason_Code,
Duration,
latestSynchedTime
)
values
(
:agent_name ,
:agent_log_id,
:agent_extension,
:Transition_Time,
:Agent_State,
:Reason_Code,
:Duration,
:latestSynchedTime
)
command on source:
Now i am getting the following error:
java.lang.NullPointerException
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.
Thanks,
Subbu.
December 14, 2015 at 8:07 PM
i forgot to add command on source:
December 14, 2015 at 8:08 PM
command on source:
October 22, 2015 at 4:35 PM
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.
Begin
MY_PLSQL_PROC(#ODI_var1, #ODI_var2, v_ret);
End;
October 26, 2015 at 6:47 PM
Unfortunately not… ODI doesn’t share the memory area for variable to get them “outputed” by a stored procedure…
March 9, 2015 at 8:40 AM
Dear Cezar,
Nice Blog. very useful stuff.
i’m newbie to odi. Using ODI 12c(12.1.2.0). 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.
Regards,
Dinesh Konuri.
January 16, 2015 at 8:32 PM
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.
Thanks…
March 20, 2014 at 12:48 AM
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
March 20, 2014 at 12:42 PM
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
January 29, 2014 at 5:40 PM
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 !
January 29, 2014 at 12:10 AM
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 ?
January 29, 2014 at 9:38 AM
Hi Ed,
I’m don’t know SQL Serve well… but a question. Is it possible to use that procedure in a “select” command?
February 4, 2015 at 2:21 PM
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
statement
So, your code will return a result set.
August 18, 2013 at 5:53 PM
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?
August 13, 2013 at 11:00 AM
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.
July 23, 2013 at 9:35 AM
hiee,
can any one give me an step by step understand of a user function i ODI ???
thanks in advance 🙂
vahini
July 24, 2013 at 12:54 AM
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
July 15, 2011 at 2:00 AM
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.
July 13, 2011 at 4:15 AM
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.
July 14, 2011 at 7:43 AM
can you please paste the complete code ,so we can guide you accordingly.
Thanks
June 10, 2011 at 8:29 AM
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
June 15, 2011 at 5:26 AM
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.
August 19, 2010 at 9:21 PM
Cezar,
It was nice chat with you yesterday. The solution you provided was so perfect.
Thanks alot.
Regards,
Harshad Ambekar
August 17, 2010 at 3:43 PM
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
August 17, 2010 at 8:43 PM
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?
July 20, 2010 at 11:29 PM
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…..
July 20, 2010 at 10:35 PM
ya two distict odi variable are created….
July 16, 2010 at 1:40 AM
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.
June 16, 2010 at 6:32 AM
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
June 16, 2010 at 6:43 PM
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
April 29, 2010 at 6:57 AM
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
April 23, 2010 at 4:15 PM
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 ?
March 7, 2010 at 9:42 AM
Greetings article has very much assisted, Excellent work
October 30, 2009 at 8:04 AM
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
October 30, 2009 at 7:49 AM
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
October 31, 2009 at 7:14 AM
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
October 26, 2009 at 9:28 AM
I will be waiting for your post.
Thanks,
Sam
October 26, 2009 at 4:26 AM
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…)
October 25, 2009 at 11:48 PM
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.
October 23, 2009 at 3:28 PM
Hi Cezar,
The procedure works correctly, thanks so much!!
October 22, 2009 at 10:58 AM
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
October 22, 2009 at 9:51 AM
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
October 19, 2009 at 5:44 AM
very useful stuff. Particularly scenario 3