Oracle

Determining the errors of a package body during recompile

Leave a comment

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

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply