Oracle 11 Apps (PACK) and detecting deleted records

[peternolan9]

Hi All,
I could not find a forum for PACKs so I tried here.

We recommended to the client that they simply put triggers into Oracle to detect the deletes since there are very few deletes happening...but the client would like us to research other ways of getting deletes from oracle apps...

We are extracting data from Oracle Apps 11i. We extracted from the database directly rather than buy the PACK....however, I've just been asked if the PACK could have detected deleted records.

I have one short 16 slide presentation on Oracle PACK and one of the slides has one reference to 'Available Delta Files'.

I was wondering what this 'Available Delta Files' means and if there is some capability inside Oracle 11 Apps to create a delta file so that deletes can be detected using the PACK (or whatever) without having to go to CDC for Oracle.

Any other suggestions/ideas most welcome...

[peternolan9]

Hi All,
given the underwhelming response to this question....the next question kind of struck me.....has anyone else out there actually done an Oracle 11 apps -> DW project in DataStage??? (Please, someone say yes...)

Hi All,
I could not find a forum for PACKs so I tried here.

We recommended to the client that they simply put triggers into Oracle to detect the deletes since there are very few deletes happening...but the client would like us to research other ways of getting deletes from oracle apps...

We are extracting data from Oracle Apps 11i. We extracted from the database directly rather than buy the PACK....however, I've just been asked if the PACK could have detected deleted records.

I have one short 16 slide presentation on Oracle PACK and one of the slides has one reference to 'Available Delta Files'.

I was wondering what this 'Available Delta Files' means and if there is some capability inside Oracle 11 Apps to create a delta file so that deletes can be detected using the PACK (or whatever) without having to go to CDC for Oracle.

Any other suggestions/ideas most welcome...

[ogmios]

Have not done Oracle Apps 11i -> DW project on DataStage. But unless you can 'freeze' Oracle for the time of your extracts you absolutely definitely need CDC or risk losing some data along the way some of the time.

Just my 2 cents.

Ogmios

[peternolan9]

Hi Ogmios,
can't use CDC. Oracle Apps does not have primary keys defined and CDC requires primary keys....we are ensuring we get all data by having a timestamp range extracted and recommending a quiesce of Oracle prior to starting incremental extracts.

We have been told Oracle apps cannot even use the Oracle CDC product because of lack of primary keys....

Have not done Oracle Apps 11i -> DW project on DataStage. But unless you can 'freeze' Oracle for the time of your extracts you absolutely definitely need CDC or risk losing some data along the way some of the time.

Just my 2 cents.

Ogmios

[chulett]

If you can't use CDC, then it seems like there are only two other options that I can think of... not that there may not be others, of course.

The first would be the triggers you mentioned earlier. It would take some effort to get setup, but then you would have complete control over how it was implemented.

The other option might be another CDC 'like' product, but one that utilizes the Oracle logs. I believe that Ascential still has a product that works in this fashion, but it is getting a little long in the tooth and may no longer be sold. Quest Software has a product called SharePlex that may help, from what little I remember about it. There's another product a colleage of mine has mentioned in the past, I've have to ping him and see if it may help as well. At worst case, I understand the Log Miner API is an open API, but I'm not sure I'd want to go down that particular path, rolling your own.

[chulett]

Peter - on the off chance you are still in search of information on this topic, you may want to check out Replicate1 from MIMIX... formerly called Omni Replicator. Should be able to handle your requirements, I believe.

[peternolan9]

Hi Craig,
thanks, I heard about this from another source. The last time I talked to these guys they said they 'replicate' and not 'propagate'.

We are currently still recommending to the client to go with triggers...

Peter - on the off chance you are still in search of information on this topic, you may want to check out Replicate1 from MIMIX... formerly called Omni Replicator. Should be able to handle your requirements, I believe.