5 Replies Latest reply: Dec 13, 2012 4:02 AM by Sai RSS

    delete rows in the target table using odi interface

    Sai
      Hi gurus,

      I am using ODI 11.1.1.5 and my source and target databases are Oracle 11gR2. Is it possible to know delete rows in the target table using odi interface?

      The scenario is I have a source table by name 'A' in my source database for which i got 'Read only access', and in some other database I got a table by name 'B' for which I got
      'Read and Write' access. At any point the data in the Target table should be mirror image of source table.

      I can handle Inserts and Updates, but I don't know how handle delete operations on the source where the expected result is "corresponding records should be deleted in the Target table". I know with CDC I can achieve this but in CDC it creates a trigger and view on source table which is not possible in my case.

      Please share your ideas,

      Regards,
      Sai.
        • 1. Re: delete rows in the target table using odi interface
          Bhabani Ranjan
          If I understand your requirement properly then you can create one extra step to achieve this.

          You delete those rows from target table where target key column not in in your source key column.
          • 2. Re: delete rows in the target table using odi interface
            Sai
            Hi Bhabani,

            Thanks for the reply,

            You understood the requirement correctly, the extra step with code deleting the rows in the target table will suffice the requirement.

            I am using IKM SQL Incremental Update, Could you please help in writing the code for that step.

            Thanks in Advance

            Regards,
            Sai.
            • 3. Re: delete rows in the target table using odi interface
              Bhabani Ranjan
              Create a new step prior to Insert new rows of your IKM. Put below codes on command on target. (better you duplicate the insert new rows step and move it to just before that step)

              delete from      <%=snpRef.getTable("L","TARG_NAME","A")%>
              where <%=odiRef.getColList("", "[COL_NAME]", "\n\tand\t", "", "UK")%> not in (
              select <%=odiRef.getColList("", "[COL_NAME]", "\n\tand\t", "", "UK")%>
              from     <%=snpRef.getTable("L","INT_NAME","A")%>
              where     1=1)
              • 4. Re: delete rows in the target table using odi interface
                Sutirtha Roy
                Customize your IKM .
                Add a step like Delete From Target

                The code for this step will be

                delete from <%=snpRef.getTable("L","TARG_NAME","A")%>
                where
                (<%=snpRef.getColList("","[COL_NAME]", ", ", "", "(UD1)")%>) IN
                (select <%=snpRef.getColList("","[COL_NAME]", ", ", "", "(UD1)")%> from <%=snpRef.getTable("L","INT_NAME","A")%>)

                How it works ?

                In your interface , go to your Target table.
                Click on to the columns which will be part of your Delete Flag paramter.
                At the properties panel , check UD1 button.

                Whn you run the interface it will generate code like

                delete from <Target_Table> where (<COLUMN_MARKED_IN_UD1>) IN
                (select <COLUMN_MARKED_IN_UD1> from <I$_TABLE_USED>)

                Thanks,
                Sutirtha
                • 5. Re: delete rows in the target table using odi interface
                  Sai
                  Thanks Bhabani/Sutirtha,

                  It is working perfectly, Thanks a lot for your replies.


                  Regards,
                  sai.