Oracle provides several views to find the exact errors created during recompile of a package or any oracle programming unit. This procedure show one method of seeing the errors during recompilation of a package.
1. Logon to your oracle server as the oracle software owner.
[root@mylinux ~]$ su – oracle
Password:
[oracle@mylinux ~]$
2. Logon to Oracle SQL*PLUS as sysdba.
[oracle@mylinux ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 21 14:12:47 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. Execute recompile of your package. NOTE the error generated.
SQL> alter package larry.test_proc compile;
Warning: Package altered with compilation errors.
SQL>
4. Execute a select against the table DBA_ERRORS referencing the name of your package with the following statement:
SQL> set linesize 120
SQL> column text format a50
SQL> select line, position, text from dba_errors where name=’test_proc’;
SQL> SQL>
LINE POSITION TEXT
———- ———- ————————————————–
9 14 PL/SQL: ORA-00942: table or view does not exist
8 5 PL/SQL: SQL Statement ignored
23 18 PL/SQL: ORA-00942: table or view does not exist
22 9 PL/SQL: SQL Statement ignored
40 62 PL/SQL: ORA-00942: table or view does not exist
37 9 PL/SQL: SQL Statement ignored
51 14 PL/SQL: ORA-00942: table or view does not exist
50 5 PL/SQL: SQL Statement ignored
5. In this example the error is that object being referenced does not exist.
The two main reasons for this error are:
– The object really does not exist.
– The user does not have access/permission to object.
6. Find the line being referenced through access to table DBA_SOURCE with the following statement:
select text from dba_source where name=’test_proc’ and line in(9,23,40,51);SQL>
TEXT
————————————————–
FUNCTION get_results(v_ct1 NUMBER, v_ct2 NUMBER ) RETURN NUMBER;
FROM larry.test_tab
FROM larry.mv_test_tab
FROM larry.mv_test_tab
SQL>
7. From this results we can see that the user account ‘test’ does not have access to the table larry.test_tab
and materialized view larry.mv_test_tab. Grant permission for these objects and re-execute the compile command.
SQL> grant select on larry.test_tab to test;
Grant succeeded.
SQL> grant select on larry.mv_test_tab to test;
Grant succeeded.
SQL>
NOTE: Repeat the process if more errors or generated or the original error is not fix.
8. Re-execute compile command.
SQL> alter package larry.test_proc compile;
Package altered.
SQL>
9. This completes user of Oracle tables to diagnosis problem with recompile of package or program unit.
Larry Catt