Calling Oracle PL/SQL sotred procedure from ODI with If-Else statment

Question

Issue: I am supposed to call and execute a PL/SQL procedure which is previously defined in DB and requires an input value. In addition, there must be a checking statement that controls the input (which will later provide by entering it at runtime) is not zero. (This is a business condition even though it is possible it is not logically needed.)

Solution:

I have created an ODI variable with a background code, used this variable as input value of the called procedure. I mean, if the variable name is tst_var:

begin 
my_schema.procedure_name(#ODI_PROJECT.tst_var);
end;

I have created an ODI package and put these ODI variable and procedure sequentially in it. Also I have selected type of set variable for my variable. The output is as following:

This stream that I explained, works well. But as it is evident, there is no checking statement. Can anyone help me with issue that how can I add an IF-ELSE statement to control the value of variable tst_var?

Actually, if I want to put my thought down on paper, it would be:

current_Date= TO_CHAR(TO_DATE(SYSDATE - 1),'YYYYMMDD');
if #ODI_PROJECT.tst_var<> 0 then my_schema.procedure_name(#ODI_PROJECT.tst_var);
else my_schema.procedure_name(current_Date);

Now, I have no idea of implementing the equivalent of above logic in ODI.

JeromeFr · Accepted Answer · 2020-10-28 16:21:01Z

1

That logic can be implemented directly in the PL/SQL block in the ODI Procedure :

DECLARE
  L_CURRENT_DATE VARCHAR2(8);
BEGIN
  L_CURRENT_DATE := TO_CHAR(SYSDATE - 1,'YYYYMMDD');
  IF #ODI_PROJECT.tst_var <> 0 THEN
    my_schema.procedure_name(#ODI_PROJECT.tst_var);
  ELSE
    my_schema.procedure_name(L_CURRENT_DATE);
  END IF;
END;

Note that:

There is no need for the TO_DATE on SYSDATE-1 as this is already a date.
The variable L_CURRENT_DATE is optional, you can also directly use my_schema.procedure_name(TO_CHAR(SYSDATE - 1,'YYYYMMDD'));

answered Oct 28, 2020 at 16:21

JeromeFr

1,9282 gold badges17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Atefeh Over a year ago

Such a great solution! Thanks

Collectives™ on Stack Overflow

Calling Oracle PL/SQL sotred procedure from ODI with If-Else statment

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related