3 Replies Latest reply on Jun 29, 2012 10:51 PM by damorgan

    Orace CDC tables (built in package)

    946696
      Hi,

      Currently I am working on a project about the CDC(Change Data Capture) tables: after I enable the cdc tables in the oracle database for some tables, I need to query some data from these cdc tables. since this related to very complex pl sql, so I am wondering are there some built in packages which allow me to use directly to query some useful data from the cdc tables?


      Please let me know if you know something, thank you very much.



      here is a simple example:

      there are several tables about a users application to some program:

      the online application form will be saved in the table: application
      Now I enable the cdc table of application table, and now I have the cdc table: application_cdc
      this cdc table will record any changes of the application table that the applicant made,
      Now I need to get some table from table: application_cdc,

      is there some good built in functions or procedure that i can use directly?
        • 1. Re: Orace CDC tables (built in package)
          damorgan
          Are there packages that allow you to query useful CDC tables? Useful for what? In what version? For Synchronous, Asynch HotLog, or Asynch AutoLog? Related to publishers or subscribers?

          In other words there is no way to answer your question as written.

          My recommendation is that you start here:
          select object_name
          from dba_objects
          where object_type in ('TABLE', 'VIEW')
          and object_name like 'CDC%'
          order by 1;
          
          select object_name
          from dba_objects
          where object_type in ('TABLE', 'VIEW')
          and object_name like 'DBA%QUEUE%'
          order by 1;
          Otherwise there are some interesting APIs in DBMS_CDC_UTILITY but I suspect it is not what you are looking for:
          http://www.morganslibrary.org/reference/pkgs/dbms_cdc_utility.html

          For CDC Demos go to Morgan's Library
          www.morganslibrary.org/library.html
          • 2. Re: Orace CDC tables (built in package)
            946696
            Hi,

            Thank you for the information, and sorry for the confusion, yes, I am looking for the packages that allow me to get some information from those cdc tables(I already have cdc tables with change records in it). what you told me about the DBMS_CDC_UTILITY package is useful, but it is too general(high level) that I can not use.


            For the CDC tables I have, there are many changes records over there, I just need some useful records(rows), now it is difficult for me to write plsql to get it directly.



            The Oracle DB I am using is 11g, Synchronous, and related to the subscriber, and I have the dw_.._cdc tables already, if there are not so many built in package for me to query the dw_.._cdc tables, what I can do is:

            (1) to write complex plsql directly to query these cdc tables

            or

            (2)recreate the cdc tables(many original tables combined to one table): to modify the publisher part to make the data(for subscriber) more related to what I want


            Im new to the cdc concept, please give me some advise, Thank you.
            • 3. Re: Orace CDC tables (built in package)
              damorgan
              Simple advice: Start writing SQL because what you are looking for does not exist.