CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle-data-integrator
Atefeh
Atefeh

Reputation: 181

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

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:

  1. 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;
  1. 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:

enter image description here

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.

Upvotes: 1

Views: 810

Answers (1)

JeromeFr
JeromeFr

Reputation: 1928

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:

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

Upvotes: 1

Related Questions