10 Replies Latest reply: Dec 11, 2012 12:38 AM by user132819 RSS

    Reg : CDC on Views

    user132819
      Hi All ,

      Can anyone help me on this

      I have a requirement like to implement CDC on Inline queries or Views , can we achieve this or any other workaround .

      Thanks
        • 1. Re: Reg : CDC on Views
          _Phil
          Have a look here:

          cdc
          • 2. Re: Reg : CDC on Views
            user132819
            Hi Phil ,

            The link which you have provided is not useful as it has no solution , they finally ended with no solution .

            Thanks
            • 3. Re: Reg : CDC on Views
              user132819
              Hi Phil ,

              Please help me on this

              How can we create View in Source in ODI ?

              Thanks
              Satya
              • 4. Re: Reg : CDC on Views
                A. Drieux
                I don't think you can use CDC on view natively.

                Actually, you will be able to add a view as a journalized datastore, but when you will start the CDC, it may fail. Because the triggers (if you use the JKM based on triggers..) cannot be created. A trigger cannot be created based on a database view since a view doesn't really store records.

                There may be a solution : you should create the triggers by yourself. When the trigger detects new insert / update / delete in the source TABLE, it inserts keyin the J$ table you've created for your view. I don't know if I'm clear.


                edit : I've looked the link given by Phil, and I think you should try. Because GRK didn't try the solution of "INSTEAD triggers" given by Sutirtha Roy. Because he failed when testing CDC on table, before testing this solution.

                Edited by: A. Drieux on 6 déc. 2012 08:49
                • 5. Re: Reg : CDC on Views
                  _Phil
                  Apologies if you didn't find the link helpful - there was a potential solution there which wasn't actually attempted.

                  Have you considered the use of fast-refreshable materialized views rather than ODI CDC? It might be a neater solution.
                  • 6. Re: Reg : CDC on Views
                    user132819
                    Hi A. Drieux ,

                    I tried implemeting CDC on view as per Sushritha's process , i have edited JKM by replacing

                    create or replace trigger to create or replace INSTEAD OF trigger , and imported .

                    Now when i tried to add CDC , i facing issue when i Start Jounal , it is giving below issue

                    org.apache.bsf.BSFException: exception from Jython:
                    Traceback (most recent call last):
                    File "<string>", line 49, in <module>

                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
                         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
                         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
                         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
                         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
                         at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
                         at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
                         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
                         at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
                         at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
                         at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                         at java.lang.reflect.Method.invoke(Method.java:597)

                    java.sql.SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option


                         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
                         at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:561)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
                         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
                         at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
                         at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
                         at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
                         at java.lang.Thread.run(Thread.java:662)
                    Caused by: Traceback (most recent call last):
                    File "<string>", line 49, in <module>

                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
                         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
                         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
                         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
                         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
                         at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
                         at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
                         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
                         at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
                         at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
                         at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                         at java.lang.reflect.Method.invoke(Method.java:597)

                    java.sql.SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option


                         at org.python.core.PyException.fillInStackTrace(PyException.java:70)
                         at java.lang.Throwable.<init>(Throwable.java:181)
                         at java.lang.Exception.<init>(Exception.java:29)
                         at java.lang.RuntimeException.<init>(RuntimeException.java:32)
                         at org.python.core.PyException.<init>(PyException.java:46)
                         at org.python.core.PyException.<init>(PyException.java:43)
                         at org.python.core.Py.JavaError(Py.java:455)
                         at org.python.core.Py.JavaError(Py.java:448)
                         at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)
                         at org.python.core.PyObject.__call__(PyObject.java:355)
                         at org.python.core.PyMethod.__call__(PyMethod.java:215)
                         at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)
                         at org.python.core.PyMethod.__call__(PyMethod.java:206)
                         at org.python.core.PyObject.__call__(PyObject.java:397)
                         at org.python.core.PyObject.__call__(PyObject.java:401)
                         at org.python.pycode._pyx26.f$0(<string>:55)
                         at org.python.pycode._pyx26.call_function(<string>)
                         at org.python.core.PyTableCode.call(PyTableCode.java:165)
                         at org.python.core.PyCode.call(PyCode.java:18)
                         at org.python.core.Py.runCode(Py.java:1204)
                         at org.python.core.Py.exec(Py.java:1248)
                         at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
                         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
                         at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
                         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
                         at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
                         at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
                         at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
                         ... 1 more
                    Caused by: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
                         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
                         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
                         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
                         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
                         at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
                         at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
                         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
                         at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
                         at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
                         at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                         at java.lang.reflect.Method.invoke(Method.java:597)
                         at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)
                         ... 33 more

                    Thanks ,
                    Karthik
                    • 7. Re: Reg : CDC on Views
                      A. Drieux
                      At witch step do you have this error ?
                      • 8. Re: Reg : CDC on Views
                        user132819
                        In Start Journal

                        Step 14 : Session Task Journalizing Jounalizing Create Trigger

                        Regards,
                        Karthik
                        • 9. Re: Reg : CDC on Views
                          PeakIndicators_Alastair
                          Paste the code being sent to the database, you are probably missing a piece of config (change set name, PK etc)
                          • 10. Re: Reg : CDC on Views
                            user132819
                            Hi ,

                            It got failed at step 14 : Create Trigger

                            Error message :

                            org.apache.bsf.BSFException: exception from Jython:
                            Traceback (most recent call last):
                            File "<string>", line 49, in <module>

                            Code :

                            triggerCmd = """
                                 create or replace INSTEAD OF trigger WORKREP_ZENDEVCNV.T$TEST_VIEW
                                 after insert or update or delete on DEVCNV_ZENPRISM.TEST_VIEW
                                 for each row
                                 declare
                                      V_FLAG     VARCHAR(1);
                                      V_ID     NUMBER;
                                 begin
                                      if inserting then
                                           V_ID := :new.ID;
                                           V_FLAG := 'I';
                                      end if;
                                 
                                      if updating then
                                           V_ID := :new.ID;
                                           V_FLAG := 'I';
                                      end if;
                                 
                                      if deleting then
                                           V_ID := :old.ID;
                                           V_FLAG := 'D';
                                      end if;
                                 
                                      insert into WORKREP_ZENDEVCNV.J$TEST_VIEW
                                      (
                                           JRN_SUBSCRIBER,
                                           JRN_CONSUMED,
                                           JRN_FLAG,
                                           JRN_DATE,
                                           ID
                                      )
                                      select     JRN_SUBSCRIBER,
                                           '0',
                                           V_FLAG,
                                           sysdate,
                                           V_ID
                                      from     WORKREP_ZENDEVCNV.SNP_SUBSCRIBERS
                                      where     JRN_TNAME = 'DEVCNV_ZENPRISM.TEST_VIEW'
                                      /* The following line can be uncommented for symetric replication */
                                      /* and     upper(USER) <> upper('DEVCNV_ZENPRISM') */
                                      ;
                                 end;
                                 """

                            # Create the statement
                            myStmt = myCon.createStatement()

                            # Execute the trigger creation
                            myStmt.execute(triggerCmd)

                            myStmt.close()
                            myStmt = None

                            # Commit, just in case
                            myCon.commit()

                            Thanks ,
                            Karthik