My Oracle Support Banner

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 later
Information 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.