Re: Debugging PL/SQL code (wrong line numbers)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/19
Message-ID: <33b29de2.26374133@newshost>#1/1

sqlplus takes the blank lines out when it compiles a package/procedure. For example:

create or replace procedure foo  

as  

begin  

null;  

end;
/

will be

create or replace procedure foo
as
begin
null;
end;
/

in sql*plus. You can use the list command in sql*plus to see the real error message. For example, you run the script to create the procedure, see there is an error, show errors to get the lineno, use 'l' to list the lines around the error:

SQL> @abc  

Warning: Procedure created with compilation errors.  

SQL> show errors
Errors for PROCEDURE FOO:  

LINE/COL ERROR

-------- -----------------------------------------------------------------
6/8      PLS-00103: Encountered the symbol "LINE" when expecting one of
         the following:
         := . ( @ % ;
 
SQL> l 5 7
  5     x := 5;
  6     this line is wrong;

  7* x := 6;

The other option, something I do to preserve the blank lines in the all_source table, is to execute:

:1,$s/^$/--/

In vi before running the pl/sql code. This turns all blank lines into --, a comment. Then, the error lineno will be the same in the editor as in the database since there are no blank lines.

You can also

select text from user_source where line between 5 and 7 and name = 'FOO'
/

to get the real lines 5-7 at any time from the data dictionary....

On 19 Jun 1997 18:56:45 GMT, aak2_at_Ra.MsState.Edu (Atif Ahmad Khan) wrote:

>
>Has anybody noticed that debugging PL/SQL code is somewhat difficult
>specially when sqlplus gives you the wrong line numbers to look for
>errors. If I missed a single quote on line 200 it would tell me to
>look at line 260 for some reason. Maybe these line numbers mean something
>else?
>
>Atif Khan
>aak2_at_ra.msstate.edu

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 19 1997 - 00:00:00 CDT