6 Replies Latest reply: Oct 12, 2012 9:07 AM by 967984 RSS

    ODI 11g CDC error

    967984
      Hi experts,

      I want to do simple CDC example on ODI 11g.

      Firstly I added a table to CDC(HR.Countries table). Then start journal.

      But I am taking an error on step "Create JV$ view." :

      ODI-1228 Task COUNTRIES (Journalizing) fails on the target ORACLE connection MYLOCALDB. Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist.
      .....

      Then here is the steps generated code :


      create or replace view STG.JV$COUNTRIES /* This view is created in the work physical schema of the current Data server */
      as
      select decode(TARG.ROWID, null, 'D', 'I') JRN_FLAG,
      JRN.JRN_SUBSCRIBER JRN_SUBSCRIBER,
      JRN.JRN_DATE JRN_DATE,
      JRN.COUNTRY_ID COUNTRY_ID,
      TARG.COUNTRY_NAME COUNTRY_NAME,
      TARG.REGION_ID REGION_ID
      from (
      select L.JRN_SUBSCRIBER JRN_SUBSCRIBER,
      L.COUNTRY_ID COUNTRY_ID,
      max(L.JRN_DATE) JRN_DATE
      from STG.J$COUNTRIES L
      where L.JRN_CONSUMED = '1'
      group by L.JRN_SUBSCRIBER,
      L.COUNTRY_ID
      ) JRN,
      HR.COUNTRIES TARG
      where JRN.COUNTRY_ID = TARG.COUNTRY_ID (+)


      I am using "STG" work schema for HR phsyical schema.

      If I use "HR" work schema for HR phsyical schema, evrything is ok. But when I use "STG", I am taking this error.

      Pls help me experts. Thanks
        • 1. Re: ODI 11g CDC error
          _Phil
          Grant select on the underlying table (HR.COUNTRIES) to STG schema
          • 2. Re: ODI 11g CDC error
            967984
            I know how to give grant but no need to grant. Thanks for your reply.
            • 3. Re: ODI 11g CDC error
              PeakIndicators_Alastair
              Hi, x2 things to checK

              Did the steps before this run OK? The STG.J$COUNTRIES is created in an earlier step , if it failed to create, then the view wont create either.
              Does the user your connecting to the source system with have access to both the Work schema (STG) and the source tables (HR?)
              • 4. Re: ODI 11g CDC error
                967984
                Yes STG.J$COUNTRIES table was created.

                But, you are right this point: I can access HR Tables with ODI_USER but I cant access STG Tables. I can access STG schema tables only with STG and system users. But I dont know why??

                HR schema you already know. Then I created STG schema for ODI work schema.

                Before I made it same way and it was successful. I dont understand why it has an error now. Pls help me.

                Edited by: 964981 on 12.Eki.2012 06:27
                • 5. Re: ODI 11g CDC error
                  PeakIndicators_Alastair
                  964981 wrote:
                  Yes STG.J$COUNTRIES table was created.

                  But, you are right this point: I can access HR Tables with ODI_USER but I cant access STG Tables. I can access STG schema tables only with STG and system users. But I dont know why??
                  Before I made it same way and it was successful. I dont understand why it has an error now. Pls help me.
                  Well not sure why its any different 2nd time around but I think you know the problem, check your grants on STG objects to ODI_USER .
                  • 6. Re: ODI 11g CDC error
                    967984
                    You are rigth ! I have forgotten it really. ODI_USER doesnt have any privileges on any users now. Thank you so much.