Queries

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

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

• 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.

You will get the error, "A query with LOBs requires Net8, which is not detected on this PC" if you try to select from a table with LOBs and you are using an Oracle 7 or Oracle 7-compatible client. You are using an Oracle 7 or Oracle 7-compatible client if any of the following:

1. "Force SQL*Net" is checked in the Server Login window. This causes TOAD to look for an Oracle 7 client and use it, regardless of whether any other clients exist on the machine.
2. "SQL*Net compatible Net8" is checked in the Server Login window. This causes TOAD to use Oracle 7-compatible calls from whatever client it is actually using.
3. You are using an Oracle 7 client. You can determine where the dll is loaded from by going to Options > Executables and pressing the "Auto find" button for an Oracle utility – that will find the executable in the path of the home TOAD is using. See the Help file on using the TOAD Home in order to set a different home.

If you are running single statements: To turn off the prompting, uncheck View → Options → SQL Editor → Scan statements for bound variables before execution In TOAD 7.4 and up, this option is available from the right-click menu in the SQL Editor and is called "Scan SQL for Subst Vars" If you are running scripts: Include the statement SET DEFINE OFF at the top of the script. You can also escape out the & if you are running as a script. For example, the following script will prompt for a value of "c", but will select "a&b" without prompting for the first query:

set define on
set esc '\'
Select 'a\&b' from dual;
select 'a&c' from dual;

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.

The maximum value the Database Probe can handle is 9223372036854775807. For databases which have been up a long time, the values in V$SYSSTAT for physical reads may exceed that. If this occurs you will be warned about a data overflow (in Toad 8.6 and beyond - in versions prior to that you will receive an "invalid floating point operation." Restarting the database will solve the problem.

This is a new datatype in Oracle 9i and TOAD is not yet fully compatible with new Oracle 9i datatypes. Workaround - view the information by checking the login option "SQL*Net compatible Net8" and you can view the dates but not the time. You can also launch SQL*Plus from the SQL-Window menu to run the query there. Try the latest TOAD beta – we are working on displaying timestamp data correctly for the TOAD 7.5 release.

On 10g and above the dba needs to run stats on data dictionary and static tables. I did a test case on a non-statistics collected 10gr2 - run time was 20 minutes. I then collected stats and run time was 2 seconds. The commands are as follows:

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;