0

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?

  • Some generic advice (i.e., not Oracle specific): First, double-check the lines before and after the indicated line numbers. Second, if possible, comment out large portions of the code (at first; smaller as you close in on the problem area). Third - make sure Oracle's line count starts with line 1 of the file, not line one of the current procedure. Build a file (if possible) with two simple procedures, with deliberate error in the second, and confirm that the line numbers are what you'd expect. – RDFozz Jun 14 '17 at 21:33
  • Some other adivce, if the user does not have access to the tables, views, that error can popup as well in Oracle. If you execute the exact statement itself, you should find out if the user executing has permissions. – user120572 Jun 14 '17 at 21:37
  • 1
    Note that "line 5552" refers to the 5552nd line of the CREATE PROCEDURE statement, not that of the file in which the statement is present. One trick I sometimes use is "binary search": insert a blank line before the place you suspect the error might be and recompile; if the error line number changes, your blank line is above the error; if it does not, the blank line is below the error. Keep moving that blank line towards the error until you find it. – mustaccio Jun 15 '17 at 0:11
  • You need to take the reported line and search in dba|all|user_source view, never try to match up that line in your source file on OS. Furthermore, if you have a procedure that is 6000 lines ling that is few thousand lines too long (IMHO). You probably should convert it into package and while refactoring you might find that the size will decrease too. – Raj Jun 15 '17 at 11:57
  • Thanks stack exchange, and thanks @mustaccio for explaining why the line numbers don't match – mdarwin Jun 15 '17 at 19:23
2

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;
  • If it won't compile, will this still be there? – George.Palacios Jun 15 '17 at 7:38
  • @George.Palacios There is a difference between won't compile and compiled with errors. The procedure in question was compiled, but with errors, it is in the database. – Balazs Papp Jun 15 '17 at 7:48
  • I always found it to be beneficial to include 5-6 lines before and after the reported line number when listing source, it quickly shows me the context. – Raj Jun 15 '17 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.