Description
Oracle provides a powerful PLSQL Debugging capabilities with an interactive debugging tools. Oracle SQL developer provides with robust debugging capabilities with an interactive debugger. Oracle provides us with a package DBMS_OUTPUT, which allows us to send debug messages from our anonymous blocks, and subprograms onto the client applications ot the console. DBMS_UTILITY is again a built-in package from oracle with several programs to help with PLSQL Debugging and look at the formal errors stack functions, which formats the error stack and also look at the format back trace functions, which helps in getting the line number where the error occurred.
Description
DBMS_Output - DBMS_Output is a built-in package from oracle and is very useful for debugging. And these DBMS_OUTPUT. PUT_LINE procedure extensively for showing the messages to the console output.And these is the most common procedure to find and use for debugging and displaying messages. This procedure takes in a varchar2 input message parameter. The older version, which takes number, is still supported for legacy reasons and use 2CHAR function to pass in number input to this procedure. It puts an end of line marker at the end of input message and puts it in the output buffer.
Examples
[c]
DECLARE
I_num PLS_INTEGER;
BEGIN
I_num:=1234;
DBMS_OUTPUT.PUT_LINE(to_char(I_num));
END;
[/c]
After issuing a calls to the put statements, then call the New_LINE procedure to put a line marker manually at the end. If one adds a messages to the buffer using DBMS_OUTPUT. PUT calls, but don't call the New line procedure after that a new line marker calls to get line will not fetch anything.The DBMS_OUTPUT. GET_LINE procedure can be called in another subprogram to obtain a single line of message from the output buffer. And these DBMS_OUTPUT with SQL Plus will fetch the output automatically.
Description
DBMS_UTILITY is a built-in package from Oracle. FORMAT_ERROR_STACK is a procedure inside it, which returns back a formatted error stack. It gives output similar to SQLERRM. The difference is that it returns upto 2000 bytes of error stack, versus SQLERRM, which can return only 512 bytes of it, and truncates the rest. It should be placed in the exception handlers instead of the SQLERRM function.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives us the line number where the error occurred in the code, the output in the console shows us the line number where the error happened. But once we catch it in the when others exception handler then the data may be loss. Having exception handlers is a good practice, but at SQLCODE and SQLERRM, we lose the line number where the error occurred. And sometimes when the code runs in thousands of lines, it might get very difficult and time consuming to debug without the line number information. This is where the FORMAT_ERROR_BACKTRACE function comes in handy, which we place in the exception handler section.
But once we catch it in the WHEN OTHERS exception handler, the information may be lost. Having exception handler is a good practice, but at SQLCODE and SQLERRM it looses the line number information. This is where the FORMAT_ERROR_BACKTRACE function comes in handy in the exception handler.
Key Points
- PLSQL Debugging - Is a function with an interactive tools for PL/SQL.
- DBMS_OUTPUT - Is a built in package for debugging.
- DBMS_UTILITY - Is a built-in package for oracle.