CDC on Oracle requiring primary keys

peternolan9 · Post by **peternolan9** » Mon Nov 08, 2004 3:17 am

Hi All,
can't find a CDC forum here....so I guess this is the 'next best one' to put this question on?? Sorry if it's not...

We have installed and tested Change Data Capture. We are doing an Oracle Apps DW.

One thing we found, which is confirmed in the documentation, is that CDC 'requires' the oracle source table to have a primary key defined. However, Oracle Apps has no primary keys defined on any tables, only unique constraints.

It will not be possible to put primary keys onto the 100 or so tables we are taking out of oracle apps.

We are guessing there must be some 'work around' so that having a primary key is not 'required'. (We can't think of any good reason that it would be required.)

Anyone got any advice/guidance you can give us on how to get CDC to work on ORA Apps where there are not primary keys?

Much appreciation in advance..

ray.wurlod · Post by **ray.wurlod** » Mon Nov 08, 2004 2:43 pm

Which part of "requires" don't you understand?

Seriously, though, CDC uses the primary key as the mechanism for identifying the row(s) that it needs to select. I don't believe that you'll find a workaround.

If my memory serves, CDC uses a persistent queue (such as the transaction log) to identify changes. The only link back to rows from here is the primary key value; row numbers are not stored.

peternolan9 · Post by **peternolan9** » Tue Nov 09, 2004 6:31 am

Hi Ray,
the 'requires' part...

Especially given that it is perfectly possible to create a table without a primary key, but with a unique constraint, I'd have thought that if it actually needed a unique constraint there would be some way of telling it which unique constraint to look at.....at least if I wrote it, I would have done it that way!!!

(Indeed, since it is possible to create a table with no constraints at all surely the CDC product should be able to detect changes to any row that is changed? Other CDC products can do this.....it's CDC 101.....in case anyone from ASCL is lurking....)

ray.wurlod wrote:Which part of "requires" don't you understand?

Seriously, though, CDC uses the primary key as the mechanism for identifying the row(s) that it needs to select. I don't believe that you'll find a workaround.

If my memory serves, CDC uses a persistent queue (such as the transaction log) to identify changes. The only link back to rows from here is the primary key value; row numbers are not stored.

crouse · Post by **crouse** » Tue Nov 09, 2004 8:05 am

I know the developers of the Ascential CDC package. I'll clip this thread and send off to them. If anything constructive comes back, I'll send it your way.

-Craig