FAQ
We have a process that tracks changes to a table, whenever a column is
changes, pk is inserted in a AQ. There is a daemon process that does
some processing based on this value.

It may happen that in a short burst, one row may get modified multiple
times. Now we want to avoid re-processing the same row based on
entries in AQ.

Is there a way to get a ordered (rather distinct) set for dequeue by
specifying an attribute in the USERDATA part of the payload when
dequeuing?

Oracle is 9.2

TIA

Raj

Got RAC?

Search Discussions

  • Bernard Polarski at May 26, 2006 at 11:26 am
    Do you mean you want the apply process to skip rows in the queue table and jump directly to the last occurence of this row? You can alway write your own dml handler but rows are compared with the full value of every columns, that's why you activated the suplemental logs. I doubt you will earn something with your dml handler. The heaviest part in streams are the update and you want to detect them each.


    As of attibute that could palys in ordered for a single column you could use the SCN of t he user_data with something like that :




    declare


    lcr sys.LCR$._ROW_RECORD
    rc PLS_INTEGER;
    object_scb number;
    trn_id varchar2;


    begin

    for p_x in (select user_data from AQ$_
    loop

    rc:= p_x.user_data.getobject(lcr) # load the user_data into LCR

    -- use scn
    object_scn := lcr.get_scn();

    -- or use the trancaction id is a varchar - which is the sequentiel id of the user_data
    trn_id=lcr_get_trancation_id();

    now you check if there is already another another one and decide to delete this user_data or
    execute it. with lcr.execute;

    end loop;
    end;


    all this will take you far in time development


    B.Polarski
    http://www.smenu.org



    -----Original Message-----
    From: rjamya
    Sent: Thursday, 25 May, 2006 9:32 PM
    To: Oracle Discussion List
    Subject: AQ dequeue question



    We have a process that tracks changes to a table, whenever a column is
    changes, pk is inserted in a AQ. There is a daemon process that does
    some processing based on this value.


    It may happen that in a short burst, one row may get modified multiple
    times. Now we want to avoid re-processing the same row based on
    entries in AQ.


    Is there a way to get a ordered (rather distinct) set for dequeue by
    specifying an attribute in the USERDATA part of the payload when
    dequeuing?


    Oracle is 9.2

    --
    http://www.freelists.org/webpage/oracle-l
  • Marius Raicu at May 26, 2006 at 12:18 pm
    Hi,
    What about using SORT_LIST and/or MESSAGE_GROUPING parameters of dbms_aqadm.create_queue_table? Otherwise, defining a user message type where you declare a column like PRIORITY/SEQUENCE and getting max from this column after dequeuing or using correlation_id with message enqueuing may be an area to explore.

    regards,

    Bernard Polarski a écrit : Do you mean you want the apply process to skip rows in the queue table and jump directly to the last occurence of this row? You can alway write your own dml handler but rows are compared with the full value of every columns, that's why you activated the suplemental logs. I doubt you will earn something with your dml handler. The heaviest part in streams are the update and you want to detect them each.


    As of attibute that could palys in ordered for a single column you could use the SCN of t he user_data with something like that :




    declare


    lcr sys.LCR$._ROW_RECORD
    rc PLS_INTEGER;
    object_scb number;
    trn_id varchar2;


    begin

    for p_x in (select user_data from AQ$_
    loop

    rc:= p_x.user_data.getobject(lcr) # load the user_data into LCR

    -- use scn
    object_scn := lcr.get_scn();

    -- or use the trancaction id is a varchar - which is the sequentiel id of the user_data
    trn_id=lcr_get_trancation_id();

    now you check if there is already another another one and decide to delete this user_data or
    execute it. with lcr.execute;

    end loop;
    end;


    all this will take you far in time development


    B.Polarski
    http://www.smenu.org



    -----Original Message-----
    From: rjamya
    Sent: Thursday, 25 May, 2006 9:32 PM
    To: Oracle Discussion List
    Subject: AQ dequeue question



    We have a process that tracks changes to a table, whenever a column is
    changes, pk is inserted in a AQ. There is a daemon process that does
    some processing based on this value.


    It may happen that in a short burst, one row may get modified multiple
    times. Now we want to avoid re-processing the same row based on
    entries in AQ.


    Is there a way to get a ordered (rather distinct) set for dequeue by
    specifying an attribute in the USERDATA part of the payload when
    dequeuing?


    Oracle is 9.2

    Regards,
    Marius


    Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 25, '06 at 7:32p
activeMay 26, '06 at 12:18p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase