Re: Debugging SPs

William F. O'Neill wrote:
> Using Powerbuilder 6.5 & Oracle 8.0.5 on WINNT. Keep getting an Oracle
> error, eg. ORA-01007(variable not found in Select) error. My SP runs 19
> pages, and am having a difficult time trying to resolve this error. Anyone
> know the best way to debug a SP?
>

Assuming you're not interested in purchasing one of the gui tools (like TOAD or GOLDEN), which have built-in PL/SQL debuggers, you can get an idea of where the error is happening with sqlplus:

1. Start sqlplus client
2. @text_file_containing_sp

You'll see the text fly by. When finished, type 'show errors' and you'll get an error (ORA-01007) along with a line number. Type 'l' (list) and sqlplus will list out the sp, complete with line numbers. Find the line number referenced in the error message and look at it plus a few lines on either side (like most compilers, it's not always 100% accurate in telling you on which line the error occurs).

You might have to increase your buffer length (size) in sqlplus -- Options / Environment -- mine is set at 2000.

Of course, the GUI tools are much easier ... but sqlplus is free (well, unless you count the thousands of dollars to buy Oracle in the first place).

If this doesn't help, the only option you have is to build it a few lines at a time (much too tedious if you do have 19 pages of code). Until it compiles, dbms_output commands will do little good. :( Received on Mon Oct 07 2002 - 18:17:44 CDT