This discussion is archived
10 Replies Latest reply: Dec 10, 2012 10:38 PM by user132819 RSS

Reg : CDC on Views

user132819 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    Have a look here:

    cdc
  • 2. Re: Reg : CDC on Views
    user132819 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    At witch step do you have this error ?
  • 8. Re: Reg : CDC on Views
    user132819 Newbie
    Currently Being Moderated
    In Start Journal

    Step 14 : Session Task Journalizing Jounalizing Create Trigger

    Regards,
    Karthik
  • 9. Re: Reg : CDC on Views
    PeakIndicators_Alastair Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points