plsqltransformationpropagationadvanced-queuing

Is it possible to specify transformation in remote database upon propagation?


F.e. in one database i have payload type(varchar2, number). Upon propagation i added transformation, specifying that this payload needs to be transformed to sys.xmlType, because i have different nls_length_semantics between databases and i can't simply propagate these object types. So far so good, i've tried that and it propagates successfully. What i want to achieve is that, that sys.xmlType payload be transformer to type(varchar2, number) upon enqueueing in remote database. Is it possible?

So all in all it would look like this:

  1. Enqueue message to local queue;

  2. Propagation starts;

  3. Payload is transformed from object type to sys.xmlType;

  4. Message's is being enqueued to remote database;

  5. Payload is transformed from sys.xmlType to objectType;

  6. Message is enqueued.


Solution

  • Oracle documentation says:

    A transformation can be specified during enqueue, to transform the message to the correct type before inserting it into the queue. It can be specified during dequeue to receive the message in the desired format. If specified with a remote subscriber, the message will be transformed before propagating it to the destination queue.

    So it seems the only way to achieve transformation in remote database would be to create temporary aq table in remote database, start up propagation in that remote aq, and specify transformation uppon propagation.