8 Replies Latest reply: Sep 9, 2014 2:11 AM by user10220907 RSS

    Extracction Issue in ODI Interface

    user10220907

      Hi All

       

      My source System is Oracle EBS and my target db is also Oracle 11g.

       

      In Sql developer i am executing following query to get data from source table in  same session  else i will be returned as 0 records fetched.

       

      Insert into APP.Fnd_Sessions (Session_id,effective_date) (select userenv('sessionid'),sysdate from dual)

      select * from apps.<table name>

       

      same logic i need to implement to populate my target table.

      so i did the following

      1. created a procedure which will execute the insert statement in source

      2. Created mapping using source table

      3 created a package which execute procedure->interface.

      package is executing successfully but no data is getting inserted in target table.

       

      need help on this.

       

      regards

      Gourisankar

        • 1. Re: Extracction Issue in ODI Interface
          Nishikant-Oracle

          Hi

          One way I can think of executing this is:

          In the first procedure 'which will execute the insert statement in source', change the Target transaction to 'Transaction 0' and target commit to 'No Commit'. Execute the package again and see if the data gets populated.


          I think it will not work since the mapping will not execute the insert statement in the same transaction.

           

          In that case, is it possible for you to write an Insert command to replace the Mapping which you have created?

          If yes, then in the same procedure which inserts the statement in source, add another command for inserted the data from this source to target. Choose the proper technology and select the Target transaction to 'Transaction 0' and target commit to 'Commit'.

           

          Your procedure will look something like this for the first and second command:

          Now, the package will only have the procedure. Since both the commands will be executed in the same transaction, the data should get populated in the target table.

           

          Regards

          Nishikant

          • 2. Re: Extracction Issue in ODI Interface
            user10220907

            Hi Nishikant

             

            Thanks for the reply

             

            Actually my package contains 1 procedure which executes insert statement at source and 1 interface.

            As you  said to make it in one session i changed LKM and added a new step at beginning which executes insert statement in the inteface itself.

            Also changed the Source transaction to 'Transaction 0' and Source commit to 'Commit' as the insert statement is executing at source.

            Now i executed the interface but its the same result as before.

             

            any idea if interface creates different sessions during execution..i guess no.

             

            regards

            Gourisankar

            • 3. Re: Extracction Issue in ODI Interface
              Nishikant-Oracle

              Hi Gourisankar

              I had understood that the package had a procedure and an interface.

               

              You need to chose 'No Commit' for the first command which inserts into (initializes) the source table. If commit is done, then the transaction is already completed.

              The commit should happen after the insert into the target table.

               

              Please change the source commit to 'No commit' and test again. If it doesn't work then paste the snapshots of the changes.

               

              Regards

              Nishikant

              • 4. Re: Extracction Issue in ODI Interface
                user10220907

                Hi Nishikant

                Below are the changes done in LKM

                 

                Change-1: Added a new step

                Change1.JPG

                Chaange-2: Command on target Transaction 0 and No Commit

                Change2.JPG

                Change-3 : Insert statement for source

                Change3.JPG

                 

                and i am using the LKM in my interface

                 

                Please let me know if you need any more details

                 

                regards

                Gourisankar

                • 5. Re: Extracction Issue in ODI Interface
                  Nishikant-Oracle

                  Hi

                  Since the command is on source, please change the Source Transaction from Autocommit to Transaction 0. Also change the Source Commit to 'No Commit.

                   

                  Also, check that the command for the insert into the I$ table (or the target table whichever happens first) is in the same transaction.

                   

                   

                  If this doesn't work, then check the command in IKM which inserts data. It will probably be the insert into I$ table. Add this insert into source statement in the command on source in the same command, and keep transaction as Transaction 0 and commit as 'No Commit'.

                   

                  Whenever you make a change in a KM, look at other commands and follow the same standard.

                   

                  I think the best thing to do will be to use the original procedure which you had created, and in that keep transaction as Transaction 0 and commit as 'No Commit'. And execute the Interface after removing the new step which has been added in the LKM. In my project, when ever we have such requirement, we have such commands in two transactions (0 and 1), with 'No commit', followed by the Interfaces.

                   

                  Till now I can understand that the commit is being done to close the transaction before the data is read.

                   

                  Thanks

                  Nishikant

                  • 6. Re: Extracction Issue in ODI Interface
                    user10220907

                    Hi Nishikant

                     

                    I think you are right.

                    I think the best thing to do will be to use the original procedure which you had created.


                    In procedure i have created 2 steps.

                    Step1: Executes insert statement.--Commit as you suggested( Transaction 0 'No Commit' in both source and target)

                    Step2: Executes a count(*) statement.

                    But Step2 does not return any record which tells that the session is not created properly or as you said transaction is closed before executing Step2


                    any suggestion how to debug it !!

                     

                    regards

                    Gourisankar

                    • 7. Re: Extracction Issue in ODI Interface
                      Nishikant-Oracle

                      Hi

                      Not sure why this isn't working.

                      Are you inserting the result of the count(*) statement into a table? Can you try doing this to see if the result is still 0.

                       

                      Also, can you do both these steps in the same command. "insert of the count(*)" in the 'command on target' and the "insert for initializing" in the 'command on source'.

                       

                      Regards

                      Nishikant

                      • 8. Re: Extracction Issue in ODI Interface
                        user10220907

                        Hi Nishikant

                         

                        Sorry for replying so late. I have inserted count(*) and found the following at IKM

                         

                        Scenario 1: User1.View1(created at source by LKM)--Getting some count

                        Scenario 2: User2(Traget DWH).Scenario(scenario of View1)--Count is 0

                         

                        this is because insert access is provided to User1 at source. And during select at IKM ODI is generating a select query as select <column name> User2.Scenario

                         

                        Any idea how to provide insert access to User2 which is in a different environment.

                         

                        regards

                        Gourisankar