How to easily generate execution plans for statements containing variables

I'm a user of both TOAD for SQL and Oracle. In the Oracle product, if a statement in the editor has bind variables, when you execute you get prompted for the variable values and the statement executes. You see an accurate execution time and can generate/view an explain plan for the statement.

In the SQL version of the product it doesn't appear as though the same functionality exists or, if it does, I'm not familiar with how to make it happen and would really benefit from knowing how this can be accomplished. Recently I came across this article, which gave me the idea to replace my @TestVar with :TestVar and then I was prompted with a dialog allowing me to define variable values. Unfortunately, the statement duration seems to include the time taken to populate the values in the dialog. Additionally, if you then try to click the Execution Plan tab you receive an error indicating that variables must be defined. If you use a DECLARE block in the editor to define the variables you get an accurate execution duration, but the Execution Plan tab will only return the plan for the DECLARE statement which is useless.

Hey Aaron, welcome to the ToadWorld Forums.

Was playing around with my own SQL with binds, and have come across the same issues you're observing. I would personally label these as bugs that should be fixed.

I'll point Quest's Dev team to your post here. Hopefully, these issues can be addressed in a future release.