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
)
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.