6 Replies Latest reply: Sep 8, 2011 12:22 PM by Sutirtha Roy RSS

    cdc

    GRK
      Hi,

      My Source: Views created in Schema 'A' (Based on different tables in Schema 'A')
      Target: Tables Created in Schema 'B'

      Both Source and Target: Oracle DB
      ODI - 10g.

      I was able to move data from source to target. I used LKM: Sql to oracle and IKM: Oracle Incremental update, Sql Incremental update based on interfaces.

      I was able to update records and insert new records.

      But, I couldn't capture deleted records on source (i.e. from views ) and delete the same on target ?
      Is is possible by CDC technique available in ODI ? I heard that CDC cannot be applied on views (as jkm creates triggers ,drops triggers on tables) but applicable only if source is tables ?

      If possible, what kind of jkm can be used ? Priveleges required ?

      Please, guide me.

      Thank you.
        • 1. Re: cdc
          Sutirtha Roy
          >
          But, I couldn't capture deleted records on source (i.e. from views ) and delete the same on target ?
          Is is possible by CDC technique available in ODI ? I heard that CDC cannot be applied on views (as jkm creates triggers ,drops triggers on tables) but applicable only if source is tables ?
          Normally speaking you can apply CDC on a view , but only after customizing the JKM.
          Copy the JKM say JKM Oracle Simple , this is to make sure that you have a backup to fall back on.
          Now go to the step named "Create trigger"
          You will find the code as
          create or replace trigger <%=odiRef.getJrnInfo("JRN_FULL_TRIGGER")%> after insert or update or delete on ...

          change it to

          create or replace INSTEAD OF trigger <%=odiRef.getJrnInfo("JRN_FULL_TRIGGER")%> after insert or update or delete on ...

          In theory it should work , so test it and update about the outcome .

          Thanks,
          Sutirtha
          • 2. Re: cdc
            GRK
            Sure, i shall
            • 3. Re: cdc
              GRK
              @Sutirtha Roy

              Before trying on source (views) i tried to implement cdc technique on source(tables).

              My source: Oracle tables in schema A (oracle 11g)
              My target: Oracle tables in schema B.

              ODI version - 10.1.3.5.0

              My mapping was working fine. But, in order to capture deleted records and implement the same. ( i started to work on cdc )

              case1:
              Created a registry file in C:\orahome\oracledi\lib\scripting and said - python.security.respectJavaAccessibility=false
              My Environment variable ODI_JAVA_HOME poitning to C:\programfiles\java\jdk1.6.0_20
              I imported jkm - oracle simple, edited my source model journalizing mode - simple, selected km oracle simple.
              I added the source table to - cdc and started journal ( it asked me to select the subscriber ) - selected default sunopsis - now session started.

              Now, getting following error -

              org.apache.bsf.BSFException: exception from Jython:
              Traceback (innermost last):
              File "<string>", line 36, in ?
              java.lang.IllegalAccessException: Class org.python.core.PyReflectedFunction can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

                   at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)

                   at java.lang.reflect.Method.invoke(Method.java:588)

                   at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)

                   at org.python.core.PyMethod.__call__(PyMethod.java)

                   at org.python.core.PyObject.__call__(PyObject.java)

                   at org.python.core.PyInstance.invoke(PyInstance.java)

                   at org.python.pycode._pyx6.f$0(<string>:36)

                   at org.python.pycode._pyx6.call_function(<string>)

                   at org.python.core.PyTableCode.call(PyTableCode.java)

                   at org.python.core.PyCode.call(PyCode.java)

                   at org.python.core.Py.runCode(Py.java)

                   at org.python.core.Py.exec(Py.java)

                   at org.python.util.PythonInterpreter.exec(PythonInterpreter.java)

                   at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)

                   at com.sunopsis.dwg.codeinterpretor.j.a(j.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

                   at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

                   at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

                   at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

                   at com.sunopsis.dwg.cmd.DwgCmd.k(DwgCmd.java)

                   at com.sunopsis.dwg.cmd.f.z(f.java)

                   at com.sunopsis.dwg.cmd.DwgCmd.run(DwgCmd.java)

                   at java.lang.Thread.run(Thread.java:619)


              java.lang.IllegalAccessException: java.lang.IllegalAccessException: Class org.python.core.PyReflectedFunction can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

                   at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                   at com.sunopsis.dwg.codeinterpretor.j.a(j.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
                   at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
                   at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
                   at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
                   at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
                   at com.sunopsis.dwg.cmd.DwgCmd.k(DwgCmd.java)
                   at com.sunopsis.dwg.cmd.f.z(f.java)
                   at com.sunopsis.dwg.cmd.DwgCmd.run(DwgCmd.java)
                   at java.lang.Thread.run(Thread.java:619)
              • 4. Re: cdc
                Sutirtha Roy
                >
                case1:
                Created a registry file in C:\orahome\oracledi\lib\scripting and said - python.security.respectJavaAccessibility=false
                What is that registry file name ? Does it have any file extension like .txt ,.reg etc ?
                If yes then remove the file extension ... that file name should be just "registry"

                Thanks,
                Sutirtha
                • 5. Re: cdc
                  GRK
                  No, it doesn't have any extension.
                  • 6. Re: cdc
                    Sutirtha Roy
                    Hum .. not getting any idea .
                    Better you contact oracle support people and see if they can provide any help .