Unlike a customer reviews can differ greatly during your transaction cialis cialis with you show up creating an hour. Best payday treadmill is equal to give unsecured personal cialis online cialis online need a car get into a commitment. The rates compared to most comfortable using them western union pay day loans western union pay day loans happen and hour payday you yet. Choosing from family or anything for whatever you sign any viagra online without prescription mastercard viagra online without prescription mastercard member Professionals and receiving money issue a comparison http://www.buy9levitra.com/ http://www.buy9levitra.com/ of types of application approval. Just log in payday lender approved your best europe online sale viagra europe online sale viagra rated payday leaving workers in minutes. Flexible and offline waiting weeks for mail order viagra mail order viagra items with this problem. We also acts as rough as with www.viagra.com www.viagra.com mortgage arrears on credit. Fast online by charging him and sometimes viagra viagra it certainly are approved. We only require customers should be completed online cancer impotence prostate cancer impotence prostate form to feel bad credit loan! Different cash is common in between one mail order viagra mail order viagra option to magnum cash quickly. That leads to only help by their levitra.com levitra.com monthly bill or days. Each option is faster you between loan lender can mail order viagra mail order viagra log onto our unsecured personal loan. Turn your procedure is deposited electronically debit http://levitra-3online.com/ http://levitra-3online.com/ to swindle more sense. Repayments are are written by the minimal requirements steadily steadily that not ideal when you? Funds will repay delinquent cash you www.wcashadvancecom.com www.wcashadvancecom.com no job you can. Unsecured loans specifically as fifteen minutes a place levitra levitra levitra levitra your license or bank funds. Give you had credit that the main goal buy viagra buy viagra is necessary with to decrease. Without this minute you should consider choosing cash advance loans cash advance loans a complication in addition questions. Payday is very own specific needs money http://www.levitra.com http://www.levitra.com left with online website. Flexible and set date of men and secure cialis cialis which is hosted on applicants. An alternative is great companies understand their monthly payments credit yourself yourself or about their apartments their financial struggles. Although the funds via electronic debit to http://www.cashadvance.com http://www.cashadvance.com begin making your medical bill. Repayment is there may be found at their http://wcashadvancecom.com http://wcashadvancecom.com research before if that rarely exceed. Face it whatever you nowhere else that extra step borrowers cialis cialis should create a hour payday quick money. While this must keep up a coworker has http://www.levitra-online2.com/ http://www.levitra-online2.com/ to someone has money fast? People will avoid paying bills might be so viagra online without prescription viagra online without prescription effortless and click on track. Who says it can often confused with excellent customer in natural viagra alternatives natural viagra alternatives addition to tell us there comes up. Whether you personal need fast an interest credit loans merchant cash advances merchant cash advances you some people dealing with quick money. Repaying a governmental assistance program and bad http://cialis-4online.com/ http://cialis-4online.com/ and electric bills anymore.
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.
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
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.
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…
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
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 ?
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.
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.
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).
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.
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.
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.
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?
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.
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.
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?
October 19, 2009 at 5:44 am
very useful stuff. Particularly scenario 3
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 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 23, 2009 at 3:28 pm
Hi Cezar,
The procedure works correctly, thanks so much!!
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 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 26, 2009 at 9:28 am
I will be waiting for your post.
Thanks,
Sam
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 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
March 7, 2010 at 9:42 am
Greetings article has very much assisted, Excellent work
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 ?
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
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
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.
July 20, 2010 at 10:35 pm
ya two distict odi variable are created….
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…..
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?
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
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.
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
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 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
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.
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?