11 Replies Latest reply: Dec 17, 2012 4:46 AM by user1277943 RSS

    Oracle DBLink Error

    user1277943
      Hello to all.
      We are facing an issue when trying to use the LKM Oracle DBlink. When simulating the code generated, and examining the statement creating the link we get the following code :

      create database link     
      connect to          
      identified by          "<@=snpRef.getInfo("SRC_PASS") @>"
      using          ''

      thus it is missing the DBLink name, the Source Database and the connection string.
      We have defined both Source and Target Sources and shemata and tested the connections. Any ideas as to what we are doing wrong ?

      Thank you
        • 1. Re: Oracle DBLink Error
          Bhabani Ranjan
          Have you provided the dblink name in source data server (instance/dblink (data server )) ? I guess you have missed. Just provide and check it again.

          raise one error with below code and check the operator to verify it.

          raise ("\n\n\n\n <%=odiRef.getInfo("SRC_DSERV_NAME")%> \n\n\n\n\n")
          • 2. Re: Oracle DBLink Error
            user1277943
            Hello Bhabani

            Yes, we have provided the dblink name in the Physical Arcitecture-Oracle-<server-name- Instance/dblink (Data Server) field.
            • 3. Re: Oracle DBLink Error
              Bhabani Ranjan
              Can you create one procedure as given below

              command on target
              Technology: Jython
              raise ("\n\n\n\n <%=odiRef.getInfo("SRC_DSERV_NAME")%> \n\n\n\n\n")

              Command on source
              technology : oracle
              logical schema: select the source logical schema for source data server where you have given the dblink name
              no code is required here. leave it blank.


              Now save and run it. go to operator to check whether the dblink name is coming or not.
              • 4. Re: Oracle DBLink Error
                user1277943
                No, it doesn't
                It gives me the following ODI Error Codes: ODI-1217, ODI-1226 and ODI-1232.
                Any clues ?
                • 5. Re: Oracle DBLink Error
                  user1277943
                  Please disregard the last post.
                  It comes up right.
                  • 6. Re: Oracle DBLink Error
                    Bhabani Ranjan
                    Okay now check the same in your KM. If you still wont get it resolved then select the logical schema name in your command on source for that step (Create database link on target).

                    Thanks
                    Bhabani
                    http://dwteam.in
                    • 7. Re: Oracle DBLink Error
                      user1277943
                      Still no luck in KM. I've also tried the procedure and it reports the correct dblink.
                      Further invistigation showd that even though that stage and source datastores are different, ODI creates a synonym on the staging area for a view that exists at the source datastore. Strange !!!!

                      Any suggestions please, becuase I'm stuck !!!
                      • 8. Re: Oracle DBLink Error
                        PeakIndicators_Alastair
                        Thats actually as per design of the KM.
                        I much prefer Uli's take on using DBLinks , see here : http://www.business-intelligence-quotient.com/?tag=database-links-odi
                        • 9. Re: Oracle DBLink Error
                          user1277943
                          Thank you for your syggestion on Uli's solution.

                          We still need to use ODI's out-of -the-box solution. I recap by posting the simumated Task

                          Task

                          Task Type : C
                          Task Name 1: Loading
                          Task Name 2: SrcSet0
                          Task Name 3: Create database link on target
                          Exe Channel: J
                          Source Context Code: PRODUCTION
                          Source Logical Schema Name: L-SOURCE
                          Source Connection Name:
                          Source Commit Indicator:
                          Source Isolation Level:
                          Source Plan Comp: 0
                          Source Technology Internal Name: ORACLE
                          Target Context Code: PRODUCTION
                          Target Logical Schema Name: L-ODI
                          Target Connection Name:
                          Target Commit Indicator:
                          Target Isolation Level:
                          Target Plan Comp: 0
                          Target Technology Internal Name: ORACLE
                          Default Command (Destination):
                          create database link     
                          connect to          
                          identified by          "<@=snpRef.getInfo("SRC_PASS") @>"
                          using          ''

                          and pointing out that in other tasks I have'd seen any substitution api commands, but only SQL commands.

                          Any ideas, or suggestions. Are we doing something wrong here, or have we stumbled in a bug ?

                          Thank you
                          • 10. Re: Oracle DBLink Error
                            Bhabani Ranjan
                            Is it ok with creating one option in your KM ? Provide the dblink name in that option and substitute this in your KM.
                            Please ping me to discuss more on this. ( bhabaniranjan@gmail.com ) and at the end we will put the solution here.
                            • 11. Re: Oracle DBLink Error
                              user1277943
                              Thanks to bhabaniranjan mahapatra, we found out that this is just a simulation pitfal. The generated code, once the i interface is executed is correct and all dblinks are generated approprietly!!