I am working on AQ propagation from one database to another, however when i scheduled a propagation and enqueued first message to LOCAL AQ table i've got an error in DBA_QUEUE_SCHEDULES.LAST_ERROR_MSG, "ORA-25215: user_data type and queue type do not match". Note that both object types used in AQ tables is identical, for test purpose i'm using this one:
create or replace type LOCAL_OBJ_MSG as object(
test varchar2(4000))
/
and both AQ tables are also identical, i've created them using same scripts and only changed names, one LOCAL other REMOTE. LOCAL AQ table is in LOCAL database, REMOTE one is in SCHEMA_NAME.REMOTE database.
Here's scripts i've used to create AQ tables:
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24010);
begin
dbms_aqadm.stop_queue(
queue_name => 'REMOTE_iTEST');
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24010);
begin
dbms_aqadm.drop_queue('REMOTE_iTEST');
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24002);
begin
dbms_aqadm.drop_queue_table('REMOTE_iTEST', force => true);
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24010);
begin
dbms_aqadm.stop_queue(
queue_name => 'REMOTE_oTEST');
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24010);
begin
dbms_aqadm.drop_queue('REMOTE_oTEST');
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
QUEUE_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(QUEUE_NOT_FOUND, -24002);
begin
dbms_aqadm.drop_queue_table('REMOTE_oTEST', force => true);
exception
when QUEUE_NOT_FOUND then
null;
when others then
raise;
end;
/
declare
l_exist number;
cursor c_index is
select 1 from user_objects u where u.OBJECT_NAME = upper('LOCAL_OBJ_MSG') and u.OBJECT_TYPE = 'TYPE';
begin
open c_index;
fetch c_index into l_exist;
close c_index;
if l_exist = 1 then
execute immediate 'drop type LOCAL_OBJ_MSG';
end if;
end;
/
create or replace type LOCAL_OBJ_MSG as object(
test varchar2(4000))
/
begin
dbms_aqadm.create_queue_table (
queue_table => 'REMOTE_iTEST',
queue_payload_type => 'LOCAL_OBJ_MSG',
storage_clause => 'pctfree 5 pctused 90 tablespace SEPA_INTEG_AQ',
message_grouping => DBMS_AQADM.NONE,
sort_list => 'ENQ_TIME',
multiple_consumers => true,
comment => 'Incoming TEST message table.');
end;
/
begin
dbms_aqadm.create_queue (
queue_table => 'REMOTE_iTEST',
queue_name => 'REMOTE_iTEST',
queue_type => sys.dbms_aqadm.normal_queue,
retention_time => sys.dbms_aqadm.INFINITE,
comment => 'Incoming TEST messages.',
max_retries => 5);
end;
/
begin
dbms_aqadm.start_queue(
queue_name => 'REMOTE_iTEST',
dequeue => true,
enqueue => true);
end;
/
begin
dbms_aqadm.create_queue_table (
queue_table => 'REMOTE_oTEST',
queue_payload_type => 'LOCAL_OBJ_MSG',
storage_clause => 'pctfree 5 pctused 90 tablespace SEPA_INTEG_AQ',
message_grouping => DBMS_AQADM.NONE,
sort_list => 'ENQ_TIME',
multiple_consumers => true,
comment => 'Outgoing TEST message table.');
end;
/
begin
dbms_aqadm.create_queue (
queue_table => 'REMOTE_oTEST',
queue_name => 'REMOTE_oTEST',
queue_type => sys.dbms_aqadm.normal_queue,
retention_time => sys.dbms_aqadm.INFINITE,
comment => 'Outgoing TEST messages.',
max_retries => 5);
end;
/
begin
dbms_aqadm.start_queue(
queue_name => 'REMOTE_oTEST',
dequeue => TRUE,
enqueue => TRUE);
end;
/
And here's scripts used to create subscribers, dequeue procedures, etc.:
LOCAL db:
begin
-- Add the remote subscriber.
dbms_aqadm.add_subscriber(queue_name => 'LOCAL_oTEST',
subscriber => sys.aq$_agent(name => 'LOCAL_oTEST_subscriber',
address => 'SCHEMA_NAME.REMOTE_oTEST@DB_LINK_NAME',
protocol => 0),
queue_to_queue => true);
-- Start the propagation of messages.
dbms_aqadm.schedule_propagation(queue_name => 'LOCAL_oTEST',
latency => 0,
destination => 'DB_LINK_NAME',
destination_queue => 'SCHEMA_NAME.REMOTE_oTEST');
end;
/
REMOTE db:
-- Create a table to store the messages received.
create table sepa_omsg_aq_demo
(received timestamp default systimestamp,
message LOCAL_OBJ_MSG);
-- Create a callback procedure that dequeues the received message and saves it
create or replace
procedure REMOTE_CALLBACK_TEST
(
context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number
)
as
r_dequeue_options dbms_aq.dequeue_options_t;
r_message_properties dbms_aq.message_properties_t;
v_message_handle raw(26);
o_payload LOCAL_OBJ_MSG;
begin
r_dequeue_options.msgid := descr.msg_id;
r_dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => descr.queue_name,
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle);
insert into sepa_omsg_aq_demo
(message)
values (o_payload);
commit;
exception
when others then
rollback;
end;
/
-- Register the procedure for dequeuing the messages received.
-- I'd like to point out that the subscriber is the one defined for the local database
begin
dbms_aq.register (
sys.aq$_reg_info_list(
sys.aq$_reg_info('REMOTE_oTEST:LOCAL_oTEST_subscriber',
dbms_aq.namespace_aq,
'plsql://REMOTE_CALLBACK_TEST',
hextoraw('FF'))
),
1);
end;
/
Sript to enqueue messages to LOCAL AQ table:
declare
enq_msgid raw(16);
eopt dbms_aq.enqueue_options_t;
mprop dbms_aq.message_properties_t;
message local_obj_msg;
begin
message := local_obj_msg('a');
dbms_aq.enqueue(queue_name => 'LOCAL_oTEST',
enqueue_options => eopt,
message_properties => mprop,
payload => message,
msgid => enq_msgid);
commit;
end;
/
Also note that in SYS.AQ$_MESSAGE_TYPES table(LOCAL db), verified status = 'F' for created propagation, and:
declare
rc binary_integer;
begin
dbms_aqadm.verify_queue_types(src_queue_name => 'local_otest',
dest_queue_name => 'schema_name.remote_otest',
rc => rc,
destination => 'db_link_name');
dbms_output.put_line('Compatible: ' || rc);
end;
/
returns 0, which means that table types aren't compatible. After i did some diging i've found that remote propagation cannot be done if LOCAL and REMOTE database NLS_LENGTH_SEMANTICS differ, however in this situation it's not the case. i've checked. Any idea's what i'm doing wrong or how can i find what differs between these two tables and how to fix it? Or maybe it can be a difference between some DB parameter value?
Oracle Database 11g Release 11.2.0.3.0
One area to check is the NLS_LENGHT_SEMANTICS parameter value on each database.
NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of characters rather in terms of bytes.
Your PL/SQL Type creation script does not implicitly use BYTE or CHAR conversion within the VARCHAR2 length. This will cause generation of the type to inherit the value as set for NLS_LENGTH_SEMANTICS.
Make sure you check your two database NLS_LENGTH_SEMANTICS value. in the event the values are different (BYTE vs CHAR) it could be the cause of the issue. You can either rebuild the type and queue on one of the database by explicitly using the BYTE or CHAR in the VARCHAR2 i.e
create or replace type LOCAL_OBJ_MSG as object(
test varchar2(4000 CHAR))
/
or change the NLS_LENGTH_SEMANTICS on one of the database to make sure they match. It is important to recompile the database after changing NLS_LENGTH_SEMANTICS