Toad调试存储过程、触发器

Starting with the 10g release, Oracle now offers a new interface, known as JDWP, for debugging PL/SQL and stored Java procedures. Toad fully supports both interfaces, but we recommend you continue to use DBMS_DEBUG unless you need to debug your Java code.

Toad supports both Oracle debugger API’s.

Debugging Requirements

In order to use the debugger, you will need to make sure the following are true:

1. SYS.DBMS_DEBUG is available.

2. The program you want to debug is compiled without errors.

3. You have execute privileges on the program(s) you want to debug.

4. On a 10g database, you have been granted the DEBUG CONNECT SESSION privilege.

5. You have at least Toad for Oracle Professional edition versions 6.3 – 9.5, or you are on at least Toad for Oracle Standard edition version 9.6.

Ok, Enough of this Textbook Stuff, How Do I Debug My Code Already!

Step 1: Load your program into the editor. 

Sounds easy enough, right? Our debugger supports both loading from file or directly from the database. If you load from file, we’ll look for the code in the database…if it’s not there, you’ll need to compile it first. Speaking of ‘program’, Toad will allow you to debug any of the following:

1. Functions

2. Procedures

3. Package Functions or Procedures

4. DML Triggers

5. Type Body Source

6. Stored Java Procedures

7. SQL*Plus scripts.

For this tutorial we will be concentrating on the first PL/SQL based objects. If you want to debug your Type Body source, you’ll need to load a PL/SQL program that makes your Type Body call as the Oracle debugger does not allow you to call it directly.

Step 2: Compile the source for Debug. 

For Oracle to be able to inspect the values of our PL/SQL program variables, it needs to collect some overhead information to assist with debugging. This is where the ‘little green bug’ button in Toad comes into play. This button acts as a toggle. With it turned on, anytime you compile your PL/SQL program it will collect the debug information. You will want to remove this information from the program by compiling it again with the toggle off before sending the code to your production instance. As the debug info represents overhead information, it could theoretically affect performance in a production environment. So in other words, you don’t want to see the following when connected to your production environments.

The Schema Browser identifies PL/SQL source that has the additional debug information.

Step 3: Set your run-time parameters.

To debug your stored procedure, Toad actually initiates execution of the program. If your program has an input parameter, then you need to supply the value, unless it has a program-supplied default value. If you are debugging a trigger, then you need to supply the column values necessary for building an INSERT/UPDATE/DELETE statement that will in turn cause your trigger to fire. If you do not specify the input values beforehand, Toad will open this window automatically when you attempt to debug or execute.

The Set Parameters Window

This dialog has had several enhancements over the years. This is a screenshot from version 9.5. Older versions do not have the interface to the Profiler built-in to the window.

Let’s take a few minutes to discuss each of the numbered areas below.

1. Procedure. The program to be executed. If you have a Package Body opened, you will see multiple functions and procedures listed here. Select the one you want executed/debugged. Ensure the code seen in area 3 executes the code you want to deal with.

2. Arguments. Your program may require several input parameters. Supply the values here. You’ll see the code in area 3 updated to reflect the values you supply in this area. If you are working with a DML trigger, you’ll see a list of columns for the affected table here. Supply values to help build a WHERE clause for area 3.

3. Code. Toad will build an anonymous block to cause your program to be called. If you are debugging or executing a DML trigger, you’ll see an UPDATE/INSERT/DELETE here instead of the PL/SQL call.

4. Transaction Control. At the end of the anonymous block, Toad supplies a COMMIT or ROLLBACK command. The default is COMMIT. This can be configured at View > Options > Debugger > Transaction Control. There is an option for ‘Prompt’. This will cause Toad to ask you each time you execute if you want to Rollback for Commit.

5. Toolbar. From here you can optionally enable the Profiler, or capture REFCURSOR and/or Collection values for display after execution. These are topics we’ll have to cover at a later date 

The information you supply here will be saved for future executions. It should also be noted that the ‘Code’ section can be manually coded by the user, so you can add any additional code you want here. Just be sure to have a call to your program so the debugger will work.

Step 4 (Optional): Set breakpoints.

What is a breakpoint? A breakpoint is an instruction to Oracle to pause execution when it reaches a certain line of code. PL/SQL is interpreted at run-time. When Oracle sees a line of code with an attached breakpoint, it will stop execution to allow a debugger to take control. It should be noted that Oracle does not interpret every line of code as an ‘executable’ line. Only lines of code that are actually ‘executed’ will have their breakpoints honored. Toad will tell you ahead of time, what lines you can add breakpoints to by placing a blue dot in the gutter. Clicking in the gutter will add a breakpoint to the line. Clicking on it again will remove the break point.

Line #7 is executable. There is no breakpoint here.

Line #7 now has a breakpoint attached.

The Breakpoints Tab

The Editor has several output panels that assist with debugging. You will want to enable the ‘Breakpoints’ tab. You can do this by mouse-right-clicking on the editor output tabs and selecting ‘Desktop Panels > Breakpoints.’

The ‘Breakpoints’ tab will display the lines that have breakpoints attached. You can add new breakpoints here, disable existing ones, or remove them altogether. You can also optionally add breakpoint ‘conditions’. A conditional breakpoint means that a line will only stop execution if a particular condition is present. Let us take a moment to dive into this topic a bit deeper.

When you open the ‘Breakpoint Properties’ window using the ‘Edit Breakpoint’ button, you’ll be able to supply a condition and/or pass count. A condition can be any expression that Oracle can evaluate. Each time this line is executed, the expression will be evaluated first, and if it evaluates to ‘True’, then the program will break. For the pass count, Oracle will keep track of the number of times the line has been executed. It will only break on on a particular execution. This is extremely useful when working with LOOPs and when you want to see what happens on a particular iteration, e.g. the 340^th time a particular line is executed. Setting the pass count will save the time it will take to manually step over the code 339 times.

Notes on Breakpoints:

1. Breakpoints will be saved for future executions.

2. You can manage breakpoint during execution.

Step 5: Start the program and control execution.

Use the ‘Execution’ toolbar items or the ‘Debug’ menu to start your program.

 This will start the program. Execution will only stop if a breakpoint is encountered or if an exception is called. You can hit this button at any point during execution. The program will start up from where it left off and go until it reaches the end of your program, another breakpoint, or an exception.

 This will ‘Step Over’ the code, one line at a time. The first time you hit the button, the program will execute and stop on the first line of code. If you hit the button again, Toad will go to the next executable line of code.

 If you are sitting on a line of code that calls another program, then hitting this button will ‘Trace Into’ that program. If you trace into a function call, Toad will open that function and allow you to debug that. If you trace into a type body call, Toad will open the type body for debug.

Right before your debug session starts, Toad will prompt you to compile ‘referenced objects’ with debug information. This will only come into play if you want to ‘Trace Into’ referenced objects inside your PL/SQL program. If you do not plan on doing this, then you can say ‘No.’ If you do decline this option, you will not be able to debug by using ‘Trace Into.’

 You can also ‘Run to Cursor.’ Place your keyboard cursor on the line of code you are interested in. Hit this button, and Toad will run to that line and stop. Be sure to have your cursor on an executable line, or this will not work.

 You can halt execution at any point by hitting this button. Please note on older 8i clients, using this feature can hang your session. This is an Oracle bug, not a Toad one.

You will be able to tell which line of code Toad has stopped on and which line of code will be executed next. Here’s how:

The blue line tells us that line 7 will be executed next. The debugger sits on a line prior to its execution. If we we’re to hit the ‘Step Over’ button, line 7 would be executed and focus would proceed to line 17.

Execution will end after you have gone through the entire program or hit the ‘Halt’ button. Please note that once a line of code has been executed, you cannot make a debugger call to ‘step backwards’ in the code to execute it again. This is an Oracle limitation. You will need to halt execution and start over if you want to run a previous line of code again.

Step 5: Using watches to interact with PL/SQL variables.

While executing your code, you will probably want to see what a particular variable is set to, or you may even want to change its value artificially to step through some ‘what-if’ scenarios. You will want to activate the ‘Watches’ tab to do this kind of work.

The ‘Watches’ tab is broken up into 3 sections:

1. ‘Smart Watches.’ A newer feature available in version 9, this panel will automatically parse the code for all of the variables and show you their values. This is a ‘quick-n-dirty’ look at your variables. If you want to do anything other than ‘look’, you’ll need to manually add a watch.

2. ‘Watches.’ These are the variables or expressions you want to inspect during run-time. You can add these by dragging and dropping from the ‘Smart Watches’ area, or you can manually add by using the ‘Add Watch’ toolbar button. You’ll see that ‘x’ is showing as ‘NULL.’ This is because line #7 has not actually executed yet. If you were to step over to the next line, ‘x’ would then show a value of ‘1234.’

3. Toolbar. What if you want to see what happens in your code if ‘x’ were equal to something else? You can artificially alter the value of ‘x’ using the debugger.

 ‘Edit Watch.’ Clicking this button allows us to modify the properties of the watch. We’ll look at that in a second.

 ‘Evaluate/Modify Watch.’ This button allows us to change the value of the variable inside the execution of the program. Let’s take a look at this in detail.

“Expression.” – this is the value that we are inspecting with the watch.

“New Value.” – type in the new value you wan to assign to the expression selected in area #1.

“Modify” – hit this button to send the value inputted in area #2.

“Result” – you’ll see the updated value here.

The new value will persist until the program is finished executing or until it changes based on a future line of code or until you modify it manually.

Watch Properties 

Using the properties of a watch will allow you to do some really cool things while debugging. Let’s finish our discussion by looking at these advanced features.

1. ‘Expression.’ This is the value that Toad will ‘watch’ for you. You can select any PL/SQL variable, or any expression of a PL/SQL variable. You could watch ‘X’ or ‘X+100.’

2. ‘Break on value change.’ Why are we debugging this code? Maybe you want to know why your program returns a value of 5 versus 15. Maybe you don’t know WHERE the program is misbehaving, but you do know that at some point, your variable gets set incorrectly. You can use the ‘Any value change’ watch break condition to tell Toad to stop execution. This will break the program based on a variable’s value changing, versus based on a particular line number. You could also choose a particular condition. So, instead of stepping through 4,000 lines of code waiting for ‘X’ to change, you can just set a breakpoint via the watch and have Toad stop execution automatically when your variable is updated. This can be a HUGE timesaver.

Notes on Watches:

1. You can see the value of a variable without using the ‘Watches’ tab. Just place your mouse cursor over the variable in the editor, and Toad will respond by showing the value.

2. Complex data types are generally not supported by Oracle’s debugger. For example, if you want to watch a REFCURSOR, you will need to declare a local variable in your code for each attribute of the cursor you want to watch.

3. If you do not compile with debug, watches will not work.

Summary

The Debugger will now be available to all commercial Toad for Oracle users starting with version 9.6. You will need to be current on maintenance to upgrade to version 9.6. To debug your PL/SQL code, you need to compile with debug info. Do not add debug information to objects in a production instance. Toad will build the anonymous block necessary to execute your PL/SQL program. You can use breakpoints to stop the execution of your program. You can use watches to ‘see’ the values of your PL/SQL variables.

from: http://docs.google.com/View?docid=dhq5wjmg_342dhsdnbdb

--End--