Print Friendly, PDF & Email

 
QUESTIONS

How to fix “PL/SQL: ORA-00942: table or view does not exist” during compilation?
How to find out all objects within another object?
How to find out all table references in a specific function or procedure?
How to find out in what database object a stored procedure or function is used?
How do I find all stored procedures or views that use a specific table?
How to view the definition of a stored procedure?

 
RELATED

 
SOLUTION

To find out all child objects use either DBA_DEPENDENCIES or DBMS_METADATA.GET_DDL.

 
TESTCASE

There are 2 objects in my test database which are having the ‘INVALID’ status

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A30

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from DBA_OBJECTS WHERE STATUS != 'VALID';

OWNER      OBJECT_NAME           OBJECT_TYPE     STATUS
---------- --------------------- --------------- --------
PUBLIC     P_SIT_AGENT_REPLACE   SYNONYM         INVALID
PARUS      P_SIT_AGENT_REPLACE   PROCEDURE       INVALID

Manually compilation of these objects fails with errors

SQL> alter procedure parus.P_SIT_AGENT_REPLACE compile;

Warning: Procedure altered with compilation errors.

SQL> sho error
Errors for PROCEDURE PARUS.P_SIT_AGENT_REPLACE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
25/1     PL/SQL: SQL Statement ignored
48/31    PL/SQL: ORA-00942: table or view does not exist
66/1     PL/SQL: SQL Statement ignored
89/31    PL/SQL: ORA-00942: table or view does not exist
531/7    PL/SQL: Statement ignored
532/34   PLS-00364: loop index variable 'RECAGN' use is invalid
539/5    PL/SQL: Statement ignored
539/8    PLS-00364: loop index variable 'RECAGN' use is invalid
636/5    PL/SQL: Statement ignored
636/40   PLS-00201: identifier 'RC.RN' must be declared

Another way to get compilation error is to use dba_errors (user_errors,all_errors) view

SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL NAME FOR A20
COL TYPE FOR A10
COL SEQUENCE FOR 99999999
COL LINE for 99999999
COL POSITION FOR 99999999
COL TEXT FOR A60

SELECT OWNER, NAME,TYPE, SEQUENCE, LINE, POSITION, TEXT FROM DBA_ERRORS 
WHERE TYPE = 'PROCEDURE' AND NAME = 'P_SIT_AGENT_REPLACE' ORDER BY LINE;

OWNER      NAME                 TYPE        SEQUENCE      LINE  POSITION TEXT
---------- -------------------- ---------- --------- --------- --------- -------------------------------------------------------
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          2        25         1 PL/SQL: SQL Statement ignored
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          1        48        31 PL/SQL: ORA-00942: table or view does not exist
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          4        66         1 PL/SQL: SQL Statement ignored
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          3        89        31 PL/SQL: ORA-00942: table or view does not exist
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          6       531         7 PL/SQL: Statement ignored
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          5       532        34 PLS-00364: loop index variable 'RECAGN' use is invalid
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          7       539         8 PLS-00364: loop index variable 'RECAGN' use is invalid
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          8       539         5 PL/SQL: Statement ignored
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE          9       636        40 PLS-00201: identifier 'RC.RN' must be declared
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE         10       636         5 PL/SQL: Statement ignored

As it might be seen from text column there are some missing tables related errors: “PL/SQL: ORA-00942: table or view does not exist“.
It means either the object is to be in current schema does not exist or the object is in another schema and you have not any privileges on it.

So how to find the “missing” objects?

 
The first method is to use DBMS_METADATA.GET_DDL procedure to retrieve an object ddl and review all used objects in it. But the code of an object could be too huge to analyze.

SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN txt FORMAT a2480 WORD_WRAPPED
SET TRIMSPOOL ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',false);

SPOOL object_name.sql
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') txt FROM DUAL;
SPOOL OFF

Another way is to use DBA_DEPENDENCIES view. Unlike using DBMS_METADATA.GET_DDL providing whole object body, the view provides reference objects only which simplify troubleshooting

COL owner FOR A10
COL name FOR A20
COL type FOR A10
COL REFERENCED_OWNER FOR A10
COL REFERENCED_NAME FOR A30
COL REFERENCED_TYPE FOR A15
COL REFERENCED_LINK_NAME FOR A30
COL DEPENDENCY_TYPE FOR A30
SELECT OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, 
       REFERENCED_TYPE, DEPENDENCY_TYPE FROM DBA_DEPENDENCIES  
WHERE  NAME = 'P_SIT_AGENT_REPLACE' ORDER BY REFERENCED_OWNER;

OWNER      NAME                 TYPE       REFERENCED REFERENCED_NAME                REFERENCED_TYPE DEPENDENCY_TYPE
---------- -------------------- ---------- ---------- ------------------------------ --------------- ------------------------------
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      PKG_ENV                        PACKAGE         HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      AGNACC                         TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      PKG_STD                        PACKAGE         HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      VALREMNSANL                    TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      VALREMNS                       TABLE           HARD
PUBLIC     P_SIT_AGENT_REPLACE  SYNONYM    PARUS      P_SIT_AGENT_REPLACE            PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      CMP_DAT                        FUNCTION        HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      CMP_NUM                        FUNCTION        HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      CMP_VC2                        FUNCTION        HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      ECONOPRS                       TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      ODCTURNS                       TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      APERIODS                       TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      DCACNTS                        TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      DCSPECS                        TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      AGNLIST                        TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      ININVOICES                     TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      ININVOICESSPECS                TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      FIND_AGENT_BY_MNEMO            PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      FIND_APERIODS_BY_BEGIN         PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      FIND_VERSION_BY_COMPANY        PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      P_DCPROCS_INC_TURN_TO_LINE     PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      P_EXCEPTION                    PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      P_LINKSALL_DELETE_FULL_IN      PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  PARUS      P_LINKSALL_DELETE_FULL_OUT     PROCEDURE       HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        OBJ$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CDEF$                          TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CCOL$                          TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CON$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        COL$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        STANDARD                       PACKAGE         HARD

From the query output I can find a possible reason of the issue. Some object is under SYS schema. So it could simply mean that PARUS user doesn’t have any privileges on these objects

------------------------------------------
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        OBJ$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CDEF$                          TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CCOL$                          TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        CON$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        COL$                           TABLE           HARD
PARUS      P_SIT_AGENT_REPLACE  PROCEDURE  SYS        STANDARD                       PACKAGE         HARD
------------------------------------------

Let’s check if there are any grants for PARUS user on SYS objects by using DBA_TAB_PRIVS table and previously found SYS objects

COL GRANTEE FOR A15
COL OWNER FOR A10
COL TABLE_NAME FOR A20
COL GRANTOR FOR A20
COL PRIVILEGE FOR A20
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM DBA_TAB_PRIVS 
WHERE TABLE_NAME IN ('OBJ$','CDEF$','CCOL$','CON$','COL$','STANDARD') ORDER BY TABLE_NAME;

GRANTEE         OWNER      TABLE_NAME           GRANTOR   PRIVILEGE
--------------- ---------- -------------------- --------- ----------
OLAPSYS         SYS        CCOL$                SYS       SELECT
CTXSYS          SYS        CCOL$                SYS       SELECT
OLAPSYS         SYS        CDEF$                SYS       SELECT
CTXSYS          SYS        CDEF$                SYS       SELECT
CTXSYS          SYS        COL$                 SYS       SELECT
OLAPSYS         SYS        COL$                 SYS       SELECT
OLAPSYS         SYS        CON$                 SYS       SELECT
CTXSYS          SYS        CON$                 SYS       SELECT
APEX_030200     SYS        OBJ$                 SYS       SELECT
ORACLE_OCM      SYS        OBJ$                 SYS       SELECT
OLAPSYS         SYS        OBJ$                 SYS       SELECT
CTXSYS          SYS        OBJ$                 SYS       SELECT
PUBLIC          SYS        STANDARD             SYS       EXECUTE

The generated output doesn’t contain PARUS user in GRANTEE column. It means PARUS is not permitted to select any data from the former SYS objects. To permit the PARUS user to select data from these tables you need to grant ‘select’ privilege on these tables to PARUS user

NOTE: An easy way to know if you have ‘access’ on objects is to try to ‘describe’ it under a user

SQL> connect parus/parus
Connected.
SQL> sho user
USER is "PARUS"

SQL> desc SYS.OBJ$
ERROR:
ORA-04043: object SYS.OBJ$ does not exist

SQL> desc sys.CCOL$
ERROR:
ORA-04043: object sys.CCOL$ does not exist

Granting select permissions on mentioned objects to PARUS user

GRANT SELECT ON SYS.CCOL$ TO PARUS;
GRANT SELECT ON SYS.CDEF$ TO PARUS;
GRANT SELECT ON SYS.COL$  TO PARUS;
GRANT SELECT ON SYS.CON$  TO PARUS;
GRANT SELECT ON SYS.OBJ$  TO PARUS;

Compile objects again

SQL> ALTER PROCEDURE PARUS.P_SIT_AGENT_REPLACE COMPILE;

Procedure altered.

SQL> ALTER PUBLIC SYNONYM P_SIT_AGENT_REPLACE COMPILE;

Synonym altered.

SET LINES 300 
SET PAGES 999
COL OWNER FOR A10
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A30

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='P_SIT_AGENT_REPLACE';

OWNER      OBJECT_NAME           OBJECT_TYPE     STATUS
---------- --------------------- --------------- --------
PUBLIC     P_SIT_AGENT_REPLACE   SYNONYM         VALID
PARUS      P_SIT_AGENT_REPLACE   PROCEDURE       VALID

 
 

Version  : 17:55 23.01.2018
Platform : Oracle Linux Server 6.8
Database : Oracle database 11.2.0.4