databaseoracle-databasetypesplsqladvanced-queuing

AQ propagation between databases with different NLS_LENGTH_SEMANTICS


Is it possible to propagate AQ messages between two databases which have different NLS_LENGTH_SEMANTICS, i.e. LOCAL database have BYTE(nls_database_parameters view) and REMOTE database have CHAR(nls_database_parameters view), if so then how? I've already tried setting session NLS_LENGTH_SEMANTICS parameter value to BYTE and then recreating REMOTE database AQ OBJECT_TYPE, overriding object type attribute declarations with BYTE and creating OID type in local database and using transform in propagation, but it didn't work out, still getting

"ORA-25215: user_data type and queue type do not match"

upon propagation.

This is object type that i'm using in AQ table's:

create or replace type obj_sepa_msg as object
(
  client    varchar2(50),
  cartridge varchar2(20),
  iban      varchar2(34),
  file_name varchar2(4000),
  data      clob
)

Solution

  • It's possible to propagate between databases with different NLS_LENGTH_SEMANTICS, but only system variable payload's, like sys.xmltype or sys.number. However you can't put them into object type if you're using database version 11.2.0.3 or less. If you need object types you could do something like this. When propagating to remote database add transformation to subscriber so it would transform your object type to sys.xmltype before propagating, note that you still need to specify that payload will be sys.xmltype. Then upon dequeing message in remote database you can specify transformation in dbms_aq.message_properties_t type, to transform sys.xmltype to your desired payload.