PLS-00201: identifier 'DBMS_AQ_SUB.GET_SUB_CURSOR' must be declared (Doc ID 2681183.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Invalid AQ related objects.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where status='INVALID';

OWNER                OBJECT_NAME                              OBJECT_TYPE          STATUS
-------------------- ---------------------------------------- -------------------- ----------
SYS                  AQ$_GET_SUBSCRIBERS                      FUNCTION             INVALID
SYS                  USER_QUEUE_SUBSCRIBERS                   VIEW                 INVALID
PUBLIC               USER_QUEUE_SUBSCRIBERS                   SYNONYM              INVALID
SYS                  ALL_QUEUE_SUBSCRIBERS                    VIEW                 INVALID
PUBLIC               ALL_QUEUE_SUBSCRIBERS                    SYNONYM              INVALID
SYS                  DBA_QUEUE_SUBSCRIBERS                    VIEW                 INVALID
PUBLIC               DBA_QUEUE_SUBSCRIBERS                    SYNONYM              INVALID
SYS                  CDB_QUEUE_SUBSCRIBERS                    VIEW                 INVALID
PUBLIC               CDB_QUEUE_SUBSCRIBERS                    SYNONYM              INVALID

Trying to compile invalid objects gives error:

SQL> ALTER FUNCTION AQ$_GET_SUBSCRIBERS COMPILE;

Warning: Function altered with compilation errors.

SQL> show errors
Errors for FUNCTION AQ$_GET_SUBSCRIBERS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
46/5     PL/SQL: Statement ignored
46/15    PLS-00201: identifier 'DBMS_AQ_SUB.GET_SUB_CURSOR' must be
        declared

A check for this object shows does not exist in database.

select con_id,owner, object_name, object_type, created, status from cdb_objects where object_name = 'DBMS_AQ_SUB' order by con_id,object_name, object_type;

Cause