Wednesday, September 26, 2007

Tracing Errors


For most of us, writing procedures, functions or say just a PL/SQL Code Block based on a given logic is wasy. The challenge lies in designing and placing the "Exception Handling" Block with the intent of capturing the correct line of Code that raised the exception so that one can them Zoom into the erroneous line and do make the necessary fixes. Now many of you would agree that this comes with experience 'n' ofcourse proactive thinking.
Now conder the following Code Snippet,

WHEN NO_DATA_FOUND
THEN dbms_output.put_line (sqlerrm);
END;

Looks familiar...? But here's a catch - this code block will keep rescuing us as far as Error String is less than 255 Characters. (The limitation with put_line() is that it can capture a max of 255 Characters).
Oracle has revolved this by its offering -DBMS_UTILITY.FORMAT_ERROR_STACK

BEGIN
error();
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
END;
/
ORA-00900: invalid SQL statement
ORA-06512: at line 2

Now, as evident from output above, the information provided in error stack is sufficent for application error logs and can be worked upon accordingly. And the best part is there's no limitation of number of characters we can capture in error stack. Makes life easy indeed :)

Wednesday, September 19, 2007

Statspack report analysis


Have you ever faced this ? ... You have your statspack report ready & are trying to work with your Statspack analyzer tool, which performs statspack report analysis & generates an html file, which is more understandable to non-DBA's too (with some charts, top-queries, recommendations, etc.) . At this moment, either your analysis tool gives up, or the website which you used for analysis, is unavailable (recently happened with oraperf, which we used) !
A solution to this, for a DBA, is to be able to read through the statspack report & identify the key areas, issues of importance . This place offers a simple statspack survival guide, to help understand the report. Hope this helps a DBA in speedy statspack analysis, instead of depending on a tool that is not working, or giving erratic results.