ORA-1722 ON DBMS_AQADM.MIGRATE_QUEUE_TABLE AFTER UPGRADE TO 12.1
(Doc ID 1961000.1)
Last updated on JUNE 15, 2022
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 <USERNAME>
identified by "<xxxxx>"
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota unlimited on users;
grant connect,resource to <USERNAME>;
grant AQ_ADMINISTRATOR_ROLE to <USERNAME>;
connect TEST_USER/test_user
-- creeate queue table and queue
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '"<SCHEMA>"."<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 => '<SCHEMA>.<QUEUE>',
Queue_table => '<SCHEMA>.<QUEUE_TABLE>',
Queue_type => 0,
Max_retries => 0,
Retry_delay => 0,
dependency_tracking => FALSE);
dbms_aqadm.start_queue('<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 = '<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 => '<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 => '<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 =>
'<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 = '<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 => '<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
-------------------- ------ -------------------- --------------------
<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 |