Oracle Database Technology

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-009

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.