How to replicate Oracle XMLTYPE columns

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • #8837
    Frank Knot
    Participant

    In order to replicate tables with columns with datatype XMLTYPE you will need to do the the following:

    1. Add action Environment /Name=HVR_VIRTUAL_COLS /Value=1 (define this for location group ‘*’ and tables ‘*’) to your channel.

    2. (Re)run Table Eplore from the GUI (you can optionally specify the names of the tables containing XMLTYPE using Table Filter. this will make Table Select run faster)

    The XMLTYPE columns should now be visible in the Properties window of the tables containing XMLTYPE columns. Note that these columns will be shown with datatype CLOB.

    3. Actions

    For Log-based capture:

    Define 2 ColumnProperties actions; The first one will be used during replication. The 2nd will be used during refresh

    a. Add action ColumnProperties /Name=xml /CaptureExpression=(select xmltype.getClobVal(t.xml) from document_xml t where t.document_id = {document_id}) /Context=!refresh

    Only define this for the capture location.

    b. Add action ColumnProperties /CaptureExpression=xmltype.getClobVal(xml) /Context=refresh

    Define this for capture and integrate locations

    For Trigger-based capture

    Define action ColumnProperties /CaptureExpression=xmltype.getClobVal({xml})

    Define this for capture and integrate locations

    Notes:

    • Define this for each table containing an XMLTYPE datatype column.
    • Replace xml with the name of the column with the XMLTYPE datatype.
    • Replace document_xml with the name of the table.
    • Replace document_id with the name of the key column of the table.

    4. Add action ColumnProperties /Name=xml /IntegrateExpression=xmltype.createXml({xml})

    Define this for each table containing an XMLTYPE datatype column.

    Only define this for the integrate location.

    Replace xml with the name of the column with the XMLTYPE datatype.

    5. Add action Integrate /DbProc /DbProcDuringRefresh

    Define this action for each table containing an XMLTYPE datatype column.

    Only define this for the integrate location.

    6. (Re)run HVRINIT

    You can either run a full HVRINIT or run certain specific parts when a full HVRINIT has already been run successfully.

    6.1 Recreate the HVR job scripts

    Select all locations (default)

    Select only Advanced Option “Scripts and Jobs” (unselect all other Advanced Options)

    This should give: hvrinit -oj -h oracle hubdb/pwd chn

    6.2 Recreate the enroll file

    Select only the capture location.

    Select only Advanced Option “Table Enrollment” (unselect all other Advanced Options)

    This should give: hvrinit -oe -lcaploc -h oracle hubdb/pwd chn

    6.3 Recreate the integrate database procedures

    Select only the integrate location.

    Select only Advanced Option “Database Procedures” (unselect all other Advanced Options)

    Select only the tables with XMLTYPE columns

    This should give: hvrinit -op -lintloc -ttbl -h oracle hubdb/pwd chn

    REMARKS FOR LOG-BASED CAPTURE:

    1. This approach does not replicate updates that change only the content of the XMLTYPE problem

    2. When using refresh or compare enable the ‘refresh’ context using -C refresh option or by checking it in the HVR Refresh dialog Context tab

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.
Test drive
Contact us