My Oracle Support Banner

Common issues when recreating the Order Feedback Queue table ASO_ORDER_FEEDBACK_T (Doc ID 603427.1)

Last updated on JULY 20, 2023

Applies to:

Oracle Order Capture - Version 11.5.8 to 11.5.10.2 [Release 11.5.8 to 11.5.10]
Oracle Order Capture - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Purpose

 

As explained in <Note 181410.1> , this is how the Order Feedback queue table is recreated :

How to Drop and Re-Create the Queue?

 Starting from Oracle E-Business Suite Application Release 11.5.8, the asoqueue.sql script can be used to drop and re-create the Queue.

Step 1 : Log in to Oracle Forms using the Quoting Sales Manager responsibility and navigate to QuickCodes. Delete or disable all the lookup entries for lookup 'ASO_ORDER_FEEDBACK_CRM_APPS'.

Step 2 : Truncate the table

sqlplus "/as sysdba"
SQL> truncate table ASO.ASO_ORDER_FEEDBACK_T REUSE STORAGE;
SQL> truncate table ASO.AQ$_ASO_ORDER_FEEDBACK_T_I REUSE STORAGE;
SQL> truncate table ASO.AQ$_ASO_ORDER_FEEDBACK_T_H REUSE STORAGE;
SQL> truncate table ASO.AQ$_ASO_ORDER_FEEDBACK_T_T REUSE STORAGE;
SQL> COMMIT;

 

NOTE : This is the recommended alternative For Customers on Oracle RDBMS ver 11.2.x, running the above truncate command  may not work. System may prevent from performing inappropriate DDL on AQ objects in 11.2 and the ORA-24005 is reported.
Hence to truncate a queue table and the associated IOTs (if it is a multi-consumer queue table) try using DBMS_AQADM.PURGE_QUEUE_TABLE which has been available since 10.1 .



DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'ASO.ASO_ORDER_FEEDBACK_T',
purge_condition => NULL,
purge_options => po);
END;
/

 
Step 3 : Grant execute rights on SYS.DBMS_AQADM and SYS.DBMS_AQ packages with Grant options to users APPS & ASO

grant execute on SYS.DBMS_AQADM to ASO WITH GRANT OPTION;
grant execute on SYS.DBMS_AQADM to APPS WITH GRANT OPTION;
grant execute on SYS.DBMS_AQ to ASO WITH GRANT OPTION;
grant execute on SYS.DBMS_AQ to APPS WITH GRANT OPTION;

 

Step 4:  Run asoqueue.sql script.

  You should run it from APPS user and with the following parameters : APPS <APPS password> ASO <ASO password> SYSTEM <system password>

$ cd $ASO_TOP/patch/115/sql
$ ls -ltr asoqueue.sql

 
(should find asoqueue.sql in this location)

Step 5: When previous steps are completed, then run :-
 

 ALTER TABLE ASO.ASO_ORDER_FEEDBACK_T DEALLOCATE UNUSED;
 ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_I DEALLOCATE UNUSED;
 ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_H DEALLOCATE UNUSED;
 ALTER TABLE ASO.AQ$_ASO_ORDER_FEEDBACK_T_T DEALLOCATE UNUSED;

 

The current note will detail several common issues that can appear during the queue re-creation.

Troubleshooting Steps

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
Purpose
 <Internal_Only>N.B. This Note contains anchors which are used in the IT Flows.  Please do not remove these anchors or delete the Note without first discussing this change with the author</Internal_Only>
Troubleshooting Steps
References

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