Error tracing

[JohnR]

I have a program that loops through most of the user objects in the database and performs functions on them. It is a standard database package that reports using dbms_output.

I start the program using the debugger. It runs then crashes with a ORA-06502 error (numeric or value error) after working for a while.

PL/SQL Developer shows me the program stack, but I would really like to ask Oracle about the variables in the program so that I can see which object my program was up to. I can't add debug output to dbms_output because that is buffered output and doesn't show when the program crashes. Is there any way of interrogating the current state of the program including variables at the crash point?

Is there another way I can view debug information without stepping through until the program crashes?

John

 

[mike]

It would be REALLY cool if this could be done. I don't know how to do it either.

Here are some of my thoughts on how to get the information you need though. Hopefully something helps, or maybe I'm totally missing the point.

For immediate output, don't use dbms_output. You can call a separate procedure to handle it. You can have the procedure be an autonomous transaction and write stuff to a table and then commit it. I've also seen where the procedure would write to a file and flush it - either way. At the same time you write to a table or file, you can do the dbms_output, or have an option to do either.

I've learned the hard way that it helps to put all loops inside of an anonymous block. If the loop errors, display which record it was working on at the time in the exception of the anonymous block. So, in your case, list the object name. That way it is easier to reproduce the error by temporarily having it loop on just that problem record.

Another thing I've done for my coding style is to output the package and function name and all input variables along with the SQL error code and message in the exceptions. A lot of people seem to disagree with that so I guess it's controversial. For me though, it really helps if a package calls another, which calls another. I know the variables and all of the circumstances around what causes the error and exactly where the error occured.

Anyway, I wish you luck in finding the error.

Mike

 

[Marco Kalter]

If you run the program in debug mode, you can press the "Run to next exception" button. When an exception occurs, the debugger will take you to the offending line of code and allows you to inspect the values at the moment of the exception.

 

[mike]

I guess it helps to try everything instead of sticking to my old habits.

I tried it and I like it!

Thanks for the info,

Mike