Router Transformation in ODI

August 29, 2012 Bhabani ODI 13

Router Transformation in ODI:

Hi Guys!!!!
Here is the post you were looking for. I had to spare few time  as I was getting so many emails on this. You can refer this post  if you are interested to use multiple interface for accomplishing the same. If you are thinking of a single interface then here is the solution.

Source Table :EMP
Target table: EMP_TGT1,EMP_TGT2,EMP_TGT3
KM: IKM Incremental Update

Please create below two tables

create table TABLE_NAME (
TID NUMBER,
TNAME VARCHAR2(30)
)
create table CONDITION_NAME(
CID NUMBER,
CNAME VARCHAR2(15)
)

Now reverse EMP,EMP_TGT1. Dont bother about that #GET_T as given in below image.We will do that part at the end.
router1
Now create 5 odi variables CHECK_C,CHECK_T,GET_C,GET_T,LOOP
router2
router3
router4
router5
router6
Create the interface with source as EMP and target as EMP_TGT1 and then execute it.
router7
router8
Once above interface will run successfully then create a filter as given in below image.
router9
Now create a new ODI procedure with below steps.
router10
router11
router12
router13
router14
Create a new option CONDITION_NAME in the procedure to provide the condition separated by commas.
Just right click on procedure and click on New Option.
router14_1
Well now save it and come to the next step of the procedure that is insert conditions and put below codes inside it.
declare
CNT NUMBER:=0;
COND VARCHAR2(40):=’<%=odiRef.getOption(“CONDITION_NAME”)%>‘;
begin
SELECT REGEXP_COUNT(COND ,’,’ ) INTO CNT FROM DUAL;
CNT:=CNT+1;
while CNT!=0 loop
INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
CNT:=CNT-1;
end loop;
end;

router15

INSERT INTO TABLE_NAME (TID,TNAME)
SELECT TSEQ.NEXTVAL,TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ‘EMP_TGT%’

router16
Next open the target datastore and modify the resource name to #GET_T. This variable will read table names dynamically.You can say this is the reverse of loading multiple files to one target table where source datastore will keep this variable.
router17
Now arrange all the object as given in below diagram.Remember 1st LOOP variable assign value=1 and in next LOOP variable increment by 1. Similarly CHECK_T variable should be greater than 0 in evaluate mode.
router19
YOu are done. Lets execute the package.Here is the session details from operator.
router20

router21
router22
router23
Thats all guys 😀 . Nothing impossible in ODI if you can develop the logic.
Comments are welcome.

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

13 Comments

  1. Thanks Bhabani, Finally I got the post . Many many thanks for this. Its correct that this is just the reverse of reading multiple files from one folder..I dont know why it didnt came to my mind.But I like the IKM Multitable Insert 😀

  2. Hi Bhabani,
    It was 100% nice workout, but in this post all the 3 target tables descriptions same..so we can acheive using even case conditions in mapping level, but how we can do data movement to multiple targets with diffrent descriptions(linke in informatica….

    Best Regards,
    Surya

  4. Thats great work Bhabani…Great logic and customization.You guys are really doing great job.Now a days i am not seeing much reply from you on OTN.

  5. declare
    CNT NUMBER:=0;
    COND VARCHAR2(40):=’‘;
    begin
    SELECT REGEXP_COUNT(COND ,’,’ ) INTO CNT FROM DUAL;
    CNT:=CNT+1;
    while CNT!=0 loop
    INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
    CNT:=CNT-1;
    end loop;
    end;
    WHEN I AM USING THE ABOVE PROCEDURE I GET THE ERROR
    ODI-1228: Task LVSH_ROUTER PROCEDURE (Procedure) fails on the target ORACLE connection LVSH_TRG.
    Caused By: java.sql.SQLException: ORA-06550: line 3, column 20:
    PLS-00103: Encountered the symbol “’” when expecting one of the following:

    HOW TO RESOLVE THE ABOVE ERROR

  6. HI,
    PLEASE GIVE ME THE REPLY FOR THE CAUSE OF ERROR

    ODI-1228: Task LVSH_ROUTER PROCEDURE (Procedure) fails on the target ORACLE connection LVSH_TRG.
    Caused By: java.sql.SQLException: ORA-06550: line 3, column 20:
    PLS-00103: Encountered the symbol “EXCEPTION” when expecting one of the following:

  7. THE TARGET CODE IS
    declare
    CNT NUMBER:=0;
    COND VARCHAR2(40):=Exception getOption(“CONDITION_NAME”) : Option does not exist (“CONDITION_NAME”) ;
    begin
    SELECT REGEXP_COUNT(COND ,’,’ ) INTO CNT FROM DUAL;
    CNT:=CNT+1;
    while CNT!=0 loop
    INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
    CNT:=CNT-1;
    end loop;
    end;

  8. Hi Bhabani,
    AM DONE THIS PROCESS……….
    STEP1:TAKING FIVE VARAIBLES
    LV_C,LV_T,LV_G_C,LV_G_T,LV_V
    STEP2:CREATED THE PROCEDURE WITH DROP TABLE TABLE_NAME,CONDITIONNAME,DROP SEQ,DROP SEQ1,CRETAESEQ,CREATE SEQ1,INSERT PROCEDURE CODE, STEP23:CREATED THE OPTION :CONDITION_NAME

  10. Hi Bhabani,

    when i try this code i get this error.

    BeanShell script error: Sourced file: inline evaluation of: “out.print(“declare\nCNT NUMBER:=0;\nCOND VARCHAR2(40):=\u2019″) ; out.print(odiR . . . ” Token Parsing Error: Lexical error at line 2, column 28. Encountered: “\u201c” (8220), after : “”:
    BSF info: INSERT CONDITIONS at line: 0 column: columnNo
    out.print(“declare\nCNT NUMBER:=0;\nCOND VARCHAR2(40):=\u2019”) ;
    out.print(odiRef.getOption(\u201cCONDITION_NAME\u201d)) ;
    out.print(“\u2018;\nbegin\nSELECT REGEXP_COUNT(COND ,\u2019,\u2019 ) INTO CNT FROM DUAL;\nCNT:=CNT+1;\nwhile CNT!=0 loop\nINSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, \u2018[^,]+\u2019, 1, CNT ));\nCNT:=CNT-1;\nend loop;\nend;”) ;

    ****** ORIGINAL TEXT ******
    declare
    CNT NUMBER:=0;
    COND VARCHAR2(40):=’‘;
    begin
    SELECT REGEXP_COUNT(COND ,’,’ ) INTO CNT FROM DUAL;
    CNT:=CNT+1;
    while CNT!=0 loop
    INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
    CNT:=CNT-1;
    end loop;
    end;

  11. Hi,
    when i try this code i got this error by running package at insert conditions

    BeanShell script error: Sourced file: inline evaluation of: “out.print(“declare\nCNT NUMBER:=0;\nCOND VARCHAR2(100):=\u2019″) ; out.print(odi . . . ” Token Parsing Error: Lexical error at line 2, column 28. Encountered: “\u201c” (8220), after : “”:
    BSF info: Insert Conditions at line: 0 column: columnNo
    out.print(“declare\nCNT NUMBER:=0;\nCOND VARCHAR2(100):=\u2019”) ;
    out.print(odiRef.getOption(\u201cCONDITION_NAME\u201d)) ;
    out.print(“\u2018;\nbegin\nSELECT REGEXP_COUNT(COND, ‘,’ ) INTO CNT FROM DUAL;\nCNT:=CNT+1;\nwhile CNT!=0 loop\nINSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));\nCNT:=CNT-1;\nend loop;\nend;”) ;

    ****** ORIGINAL TEXT ******

    [code lang=”java”]

    declare
    CNT NUMBER:=0;
    COND VARCHAR2(100):=’‘;
    begin
    SELECT REGEXP_COUNT(COND, ‘,’ ) INTO CNT FROM DUAL;
    CNT:=CNT+1;
    while CNT!=0 loop
    INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
    CNT:=CNT-1;
    end loop;
    end;
    [/code]

    • please ensure whether you are using 10g or 11g database because regexp_count was introduced in 11g
      version 1.

      instead of regexp_count(cond,’,’)
      use length(cond)-length(replace(cond,’,’,”)) if using 10 database.

Comments are closed.