Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

    Unanswered: Getting Online and Offline tables to sync

    Hi. I have a requirement for getting an offline table match with the online table. Basically, what I have to do is to get all of the missing rows on the offline table to be added from the online table (using minus/etc.). But they could also be updated rows (which I can literally handle), as they could also contain structure changes between both! and that's where the problem raises.

    Im my approach: I was trying to fire the update/insert and then handle the ORA-01789 (which is the exception that raises if I try to use minus with different structured tables), and then on the exception handler of this error drop the current offline table, and make an exact copy of the online one, but I cannot simply trap it since Im doing it inside PL/SQL with an implicit cursor (tried with explicit and it is the same) and it wraps the message with the ORA-06550 error. I know many of you will ask me "why ever bother to update/insert than simply create a new one?" .. but I want it in this way, and this is the way I have been asked for.

    So, If anyone could ellaborate on this kind of approach, I would happily appreciate that kind of help.

    Thank you!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    can you use FOR UPDATE with an offline table?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Use DDL trigger

    If you need both tables to be exact match all the time, but do not want to drop and re-create the offline table, then you could do this:

    1) Trap all DDL changes to the online table with a:

    CREATE TRIGGER ... AFTER ALTER ON SCHEMA ...
    ...Apply same changes to offline table ...

    2) Now you can deal with same structure tables in your update/insert whatever.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The_Duck, How can FOR UPDATE be of any help here ?

    LKBrwn_DBA, It is not that I can't drop the offline table, but I only want to drop it ONLY IF the structure has changed on the online one. About the trigger, thanks for the idea, will keep it in mind in the future. But what can I do now with both tables?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    put a FOR UPDATE cursor on the offline table.
    go through each possible update value against your parent table.

    example:
    Code:
    update offline_table
      set col1 = online_col1, col2 = online_col2, col3 = online_col3
    where current of offline_cursor;
    OR,

    I like the trigger idea where all updates get sent to either:
    1. a transaction table which creates update statements
    to update the offline table at a later time
    OR
    2. have the trigger auto-update the offline table for you
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Now that I think about it, I forgot to say that the offline and online tables are on different servers which lead me to believe I cannot track the changes unless I save it on a table which reside on the same db the online table is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •