Toad for Oracle – Queries Q&A

Q: Escaping the '_' Character: How do I escape out the '_' character in a query?

A: The _ is a wildcard for any single character. You must specify the escaping within the SQLstatement itself. Here's an example:

...WHERE usr_login LIKE '%\_del%' ESCAPE '\';

Q: How can I cancel long running queries?

A: If you are running version 8.5 or higher, then

• • Regardless of what options you have set, all queries are cancelable now, except for PL/SQL executed in the SE like anon blocks and such.
  • A 'Cancel' button will highlight on the SQL Editor toolbar next to the ‘Execute statement at cursor’ button.
  • To be able to cancel anon blocks via F5 runs, then you need to enable View – Options – Oracle – Transactions – Execute Queries in Threads.
     
• If you are running an older version of Toad, then
  • To make SELECT statements cancelable in the SQL Editor, check View → Options → SQL Editor Process statement in threads.  Then, during long running queries, click the Cancel button on the SQL Editor’s toolbar.
  • There are also options under View → Options → SQL Editor to “Process Update, Insert, and Delete statements in background” and “Process PL/SQL statements in background”.  These 2 options cause the SQL Editor to pop up a new window with a new connection to run DML statements and anonymous blocks.

DDL statements cannot be cancelled in TOAD.

Q: What does the View – Options – Oracle – Transactions – Execute Queries in Threads option do, and why should I use it or not?

A: If this option is enabled, then… …every tab in the SQL Editor (SE) has an underlying query object even if the tab is blank. Once you need to activate that query and execute something then the tab is given its own session. It’s the only way to ensure a truly threaded query. Once the query is finished executing then the connection remains open until the tab is closed. Clearing the grid/closing the query via F7 won’t end the connection either. It’s debatable on what would be expected given the large number of connections on one hand and the time it takes to create a connection on the other. If you used the threaded query option in the past simply to be able to cancel queries then you will be better off disabling the option. All queries are cancelable now in 8.5, except for PL/SQL executed in the SE like anon blocks and such. There are some caveats to using threaded queries. One is as you note, many sessions can be created. Another is that using threaded queries means that posted, but un-committed data is only visible to the tab which owns the query in the case of the SQL Editor. Even doing a Describe on the table that has the posted, but un-committed data will not show the posted data. The describe window does not have access to the session where the data edits were made. The scope of the session is only for the tab so you can probably think of other hazards this may cause, but if you have several long running queries that you need to run then using the threaded queries option will be a big time saver.