Last updated on FEBRUARY 08, 2017

Applies to:

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