how do I work out why my stored procedure won't compile?

Question

I have a stored procedure whose sql file is nearly 6000 lines in length.

It's part of a series of DB build scripts which are used in lots of environments to build a standard DB setup.

When I attempt to run it in one environment, it doesn't compile.

show errors;

gives me the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5552/10  PL/SQL: SQL Statement ignored
5552/22  PL/SQL: ORA-00942: table or view does not exist
5556/10  PL/SQL: SQL Statement ignored
5556/24  PL/SQL: ORA-00942: table or view does not exist
5566/12  PL/SQL: SQL Statement ignored
5566/26  PL/SQL: ORA-00942: table or view does not exist

In order to work out what's wrong, I need to figure out which table or view it can't see. However, line 5552 of my sql file is empty, and line 5566 has no table name on it.

How can I figure out which tables it can't see?

Answer 1

The database provided you the exact location of the error. All you need to do is check what is there, in the database, and not in your file.

select text from all_source where name = 'PROCEDURE_NAME' and line = 5552;
select text from all_source where name = 'PROCEDURE_NAME' and line = 5556;