Executing long running queries in Toad for Oracle

For those Toad for Oracle users that often execute long running queries using F9, they always ask me how they can still work in Toad without having to wait on the query to finish. The problem seems to be the user sees the dialog box below in Figure 1 and is unable to continue working in Toad unless the user either hits Cancel or lets the query finish executing. Neither of the above options is acceptable but there is hope. Here are two ways to execute that long running query and still use Toad in a productive way.

Figure 1. Execute dialog

The first method is an option to have Toad execute queries in the Editor using threads. Simply open up Toad Options from the View menu or the toolbar and go to the Oracle-Transactions section. The first checkbox should be checked so that queries can execute in threads (Figure 2). This will allow the user to work in Toad while the query executes since the Execute dialog in Figure 1 will not display. Keep in mind that this only works when executing a query using F9.

 

Figure 2. Toad Options

The second method involves executing the query in Toad Script Runner (TSR). TSR looks and feels like the Toad for Oracle Editor but only includes a subset of functionality. It can be run in the background or from command-line and many TSR instances can be run at the same time. You can execute scripts using TSR by clicking on the drop-down next to the   icon and select ‘Execute in Toad Script Runner’. The TSR application will open and execute your script. Results will be displayed when the query finishes execution.

If you’d like more information on anything Toad, please visit ToadWorld.com for more information and resources. Cheers!

About the Author
Todd.Phillippe
Todd Phillippe is a Quest Systems Consultant specializing in the Toad family of tools and has been a database specialist with over 20 years of IT experience working with enterprise relational database...