XML Reports Failing 

 While Running XML reports getting below error

 Error:

  [6/14/18 12:02:58 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[6/14/18 12:02:58 PM] [Thread-91] Service thread starting up.
[6/14/18 12:02:58 PM] [Thread-92] Service thread starting up.
[6/14/18 12:02:58 PM] [EXCEPTION] [OPPServiceThread0] java.sql.SQLException: ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1

 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:567)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:543)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:91)
at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)
at oracle.apps.fnd.cp.gsf.BaseServiceThread.run(BaseServiceThread.java:144)
SOLUTION:
  • Stop OPP Manager
  • Connect to Apps User

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

   COUNT(*)
----------
   10
  • Take a back up of the table :


 SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_14jun18 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

 Table created.

  • Connect to Applsys User and run below


 SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);
PL/SQL procedure successfully completed.
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

   COUNT(*)
----------
         0
  • Connect as APPS user and run below


 SQL>@$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: APPLSYS
Enter value for 2: apps
Connected.

 PL/SQL procedure successfully completed.

 SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();
Start OPP Manager and Validate.