ORA-1722 ON DBMS_AQADM.MIGRATE_QUEUE_TABLE AFTER UPGRADE TO 12.1
(Doc ID 1961000.1)
Last updated on FEBRUARY 03, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and laterInformation in this document applies to any platform.
Symptoms
Getting ORA-1722 on DBMS_AQADM.MIGRATE_QUEUE_TABLE after upgarde to 12.1
connect / as sysdba
create user TEST_USER
identified by "test_user"
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota unlimited on users;
grant connect,resource to TEST_USER;
grant AQ_ADMINISTRATOR_ROLE to TEST_USER;
connect TEST_USER/test_user
-- creeate queue table and queue
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '"TEST_USER"."TEST_QUEUE_TABLE"',
Queue_payload_type => 'RAW',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE users',
Sort_list => 'ENQ_TIME',
Compatible => '8.0.3');
-- Compatible => '10.0.0');
DBMS_AQADM.CREATE_QUEUE(
Queue_name => 'TEST_USER.TEST_QUEUE',
Queue_table => 'TEST_USER.TEST_QUEUE_TABLE',
Queue_type => 0,
Max_retries => 0,
Retry_delay => 0,
dependency_tracking => FALSE);
dbms_aqadm.start_queue('TEST_QUEUE');
END;
/
-- get info on queue compatible and enq_uid data type
set lines 100
col queue_table for a20
col column_name for a20
col data_type for a20
select qt.queue_table, qt.compatible, c.column_name, c.data_type
from user_queue_tables qt, user_tab_columns c
where
qt.QUEUE_TABLE = 'TEST_QUEUE_TABLE' and
qt.queue_table=c.table_name and
c.column_name='ENQ_UID' ;
-- enqueue should succeed
DECLARE
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message RAW(25);
BEGIN
message := hextoraw(1);
message_properties.priority := 1;
SYS.dbms_aq.enqueue(queue_name => 'TEST_QUEUE',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
commit;
END;
/
-- Dequeue should succeed
set serveroutput on
DECLARE
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
outmessage RAW(25);
BEGIN
message_properties.priority := 1;
DBMS_AQ.DEQUEUE(queue_name => 'TEST_QUEUE',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => outmessage,
msgid => message_handle);
DBMS_OUTPUT.PUT_LINE ('Message: ' || outmessage);
commit;
END;
/
-- upgrade to 10.0.0
exec DBMS_AQADM.MIGRATE_QUEUE_TABLE(queue_table =>
'TEST_QUEUE_TABLE',compatible => '10.0.0');
-- get again info on queue compatible and enq_uid data type
select qt.queue_table, qt.compatible, c.column_name, c.data_type
from user_queue_tables qt, user_tab_columns c
where
qt.QUEUE_TABLE = 'TEST_QUEUE_TABLE' and
qt.queue_table=c.table_name and
c.column_name='ENQ_UID' ;
-- enqueue should fail now with ORA-01722(invalid number)
DECLARE
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message RAW(25);
BEGIN
message := hextoraw(1);
message_properties.priority := 1;
SYS.dbms_aq.enqueue(queue_name => 'TEST_QUEUE',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
commit;
END;
/
SQL> SQL> SQL> 2 3 4 5 6
QUEUE_TABLE COMPAT COLUMN_NAME DATA_TYPE
-------------------- ------ -------------------- --------------------
TEST_QUEUE_TABLE 10.0.0 ENQ_UID NUMBER
.
SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17
DECLARE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_AQ", line 190
ORA-06512: at line 10
Changes
This was working fin on 11g and occurred only after the upgare to 12c
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review. |