Editor

1. Go to the Editor→Behaviour options.
2. Look for the languages section, select PL/SQL and hit Edit.
3. This opens another window. Go to the Highlighting tab.
4. Ensure Default is selected in the Styles and choose Custom Font for Style Type.
5. Hit the Custom Font button and change the font size.
6. OK out of the options windows.

For a more comprehensive review of all the new Editor features, click here

If Formatter Plus is licensed on the machine, then the Formatter dll will be used to format your code. Otherwise, TOAD's built-in formatter, which is only designed for SELECT statements, will be used. Using TOAD's built-in formatter to format PL/SQL will have un-intended and incorrect results. If you have a license for Formatter Plus, but TOAD doesn't seem to be recognizing it, do the following:

1. Open Formatter Plus and enter your Formatter license key.
2. Restart TOAD.

In order to use this functionality, you must go to Options > Editors and tell TOAD where your external editor is located. Rules:

• Enter the full path to the executable. Do not put quotes around the path.
• In TOAD 7.5 and earlier, after the path to the executable, you need to enter a space and a %S.

You can set a fixed or variable margin for wrapping text, and the gutter will show which lines are wrapped. The memo editor popup uses the "text" parser script instead of the "plsql" parser script that is used in so many other places in TOAD. (You only get the "text" parser script in TOAD when you load a .txt file into the procedure editor or offline editor.) To set the "text" parser to do line wrapping:

1. Open the memo editor with text that isn't HTML or XML (because those use the html parser)
2. Right-click and choose Options → Editing options
3. Under General options, choose to word-wrap text
4. Under Display options, choose to show word-wrapped lines with the |> glyph
5. At the bottom of the screen, set the word wrap column. If you set it at "0", then the text will wrap at the window border. If you set it at another number, then the text will wrap at that position, and you can drag the word-wrap column back and forth to change the word-wrap position.
6. Press OK.

Look under Options → Parser Scripts for the location of your parser script, probably \User Files\plsqlscr.txt. In that parser script, look under "%%tokens" for the value(s) of st_STRING. If there is more than one parser script showing, the one you want has the language defined as PLSQL. Under TOAD 7.5 there was only one parser script, but sometime between 7.5 and 8.0 more were added.

The "//" is a comment marker.
'text in single quotes'
"text in doubled quotes"

• __STD_PASCALSTRING: 1st line red, Other 3 not
• __STD_PLSQLSTRING: 1st 2 lines red, Next 2 not
• __STD_C_STRING: 3rd line red, Other 3 not

• __STD_PASCALSTRING and __STD_C_STRING: 1st line and 3rd line red. Other 2 not.
• __STD_PLSQLSTRING and __STD_C_STRING: 1st, 2nd, and 3rd line red. 4th line not.
• __STD_PASCALSTRING and __STD_PLSQLSTRING: Parser error when starting TOAD.

• __STD_C_STRING tells the parser that text on a single line between double-quotes is a string.
• __STD_PASCALSTRING tells the parser that text on a single line between single-quotes is a string.
• __STD_PLSQLSTRING tells the parser that any text (multiple lines or not) between single-quotes is a string.
• __STD_PLSQLSTRING and __STD_PASCALSTRING are incompatible, but either can be used in conjunction with __STD_C_STRING.

• The default setting enables __STD_PASCALSTRING and __STD_C_STRING

You need to restart TOAD after changing the parser script or your changes will not take effect.

Do not save the parser script file in Unix mode. If you do, you will get an error similar to 'Error near line 1, column nnnnnn: Script syntax error', followed by an Access Exception problem when you try to close TOAD. (8.0.6.38 anyway.)

If you have "Apply capitalization effects" turned on in your Editor Options, then we STRONGLY recommend that you do not use __STD_PLSQLSTRING, since any text that at any time during your editing that is not between single quotes will get capitalization effects applied. For example, suppose you have the following in your procedure:

a := 'my string';
c := 'my table';

Now suppose you are inserting a line, like so:

a := 'my string';
b := '
c := 'my table';

At this point in time, the word "TABLE" is no longer between a pair of quotes, according to the parser, which keeps looking on future lines for the next quote. So the word "TABLE" gets capitalized, even if you do finish typing the text:

a := 'my string';
b := 'my other string';
c := 'my TABLE';

Press CTRL + #, where # can range from 0-9. Or use the "Bookmarks" item on the right-click menu. Note: To set a bookmark, you can press CTRL + SHIFT + #

To resolve these problems, go to Edit → Editor Options and look under "General Options", under the "General Options" node, where there are several options related to tab stops. Unchecking "Auto indent" and "Tab to next column" will make the step size consistent. Unchecking "Insert TABs into text for TAB characters" and "Insert mixture of tab/space for optimal fill" will make TOAD use spaces instead of Tabs, which will prevent any problems where TOAD's tab stop difference is different from another program's.

The tab stop distance can also be set at the bottom of this page. Edit → Editor Options → General Options → Display Options → "Show control characters" lets you see where tabs or spaces are used.

To get rid of existing tabs, open your file and press "Replace". Check "Regular expressions". Choose to find "\t" and replace with "\s\s\s" to replace all tabs in the file with 3 spaces. Select Entire and press Replace All.

There was a bug in TOAD 7.5 where the "plsqlkeys.bin" file would get corrupted if you viewed the "Key assignments" tab under Editor Options. If you have TOAD 7.5.2 or earlier, please try the following:

1. Close TOAD.
2. Delete "plsqlkeys.bin" from your \User Files directory.
3. Reopen TOAD and see whether those keys now work.

These problems seem to be fixed in TOAD 7.6 There have been shortcut key assignments reported in the beta, however. We are trying to track down the source of the problem in this case.

We suspect that the file "plsqlkeys.bin" is getting corrupted. Please try the following:

1. Close TOAD.
2. Delete "plsqlkeys.bin" from your \User Files directory.
3. Reopen TOAD and see whether those keys now work.

TOAD 7.4 and 7.5 attempts to perform a describe on the object under the cursor when you hold the Alt key down and click in the editor. But the Alt key is getting captured at times that you don't intend for it to be. To turn off this behavior in TOAD 7.5, uncheck Options → Editors → Perform DESCRIBE on Alt-Click. TOAD 7.6 and up does not have this behavior for Alt-click. TOAD 7.6 attempts a describe when you hold down the Ctrl key and click in the editor. This was changed to make it consistent with the procedure editor, and TOAD is not capturing the Ctrl key unless you are actually holding it down, unlike its previous behavior with the Alt key.

We made drag-n-drop and copy/paste such that you could do either.... You can copy/paste as you are wanting from the Object Pallete - which you may find more convenient than doing f4 popups on your objects to get columns lists... Also, you are aware of the DOT lookups? i.e. type "scott.emp." you will get a popup where you can multiselect the columns you want for that table.

From Jeff Smith :

The block indicator is that (usually red) horizontal line drawn across the editor's text to separate blocks of code such as functions and procedures in a package etc. To turn it off, follow the following instructions :

View → Options → Behavior → Languages → PL/SQL → Highlighting.
Select Function Separator and hit the 'Disable' button.

Within the editor's left margin, there are other indicators that show collapsible blocks of code. These are known as block staples and to turn them off, use the following information contributed by Tom Myres :

To disables the vertical gray lines (called block staples) you have to modify every rule that draws them, because you really do want those rules, just no block staples, so:

• View → Options → Behavior → Languages → PL/SQL → Edit.
• Click on the Rules tab.
• Click on the Properties tab for that rule (inner box).
• Click on the top rule.
• Arrow down through each rule until you see a check by "draw block staple."
• Turn off as needed.
• Click back on the rule name and go back to 'Arrowing' down through the rules. Repeat as necessary.
• Click OK.

No restart of TOAD required.

You may leave some rules drawing 'block staples' if you wish.

You can search for various metacharacters in the MOE as follows :

• CTRL F (or Edit→Find) to open the Find dialogue.
• Tick the box marked Regular expressions in the Options section.
• In the Text to find editbox, type in one (or more) of the characters in the table below.
• Click OK.

The above table is stolen blatently from the TOAD help file. The topic is named Regular Expression Searches. Other help topics on this subject are :

• Character Classes
• Metacharacters
• Examples of Regular Expressions

From the TOADBeta list, answer supplied by Norm.

I just upgraded to Beta 9.1.0.62 and the highlighting for strings stopped working.
Instead of red they show up in black.

• View→Options→Editor→Behaviour
• Click pl/sql and then the Edit button.

On the highlighting tab, check what's set for STRING. That should be what you get, however, there seems to be a slight anomaly in the beta upgrade path of late, so click on the parser tab.

In my Beta 56 there's a load of stuff and near the bottom of the list of categories there's a STRING 1 and a STRING 2. I do not have STRING here, but others have had. Ok, do this next :

• If you have it, highlight STRING and DELETE it.
• If you don't have it, panic not.
• Select STRING 1 (should be above STRING 2) and see what it says on the

right for Token type and default highlighting - both should say STRING.

• Ditto for STRING 2.

I suspect your token type or default highlighting is set to NONE, so the parser parses out a string and sees that you don't want it to be highlighted.

Click OK all the way back and all should be well again.

set echo off
set heading off
set feedback off
SPOOL c:\temp\all_tmp.sql
select 'alter user ' || username || ' temporary tablespace TEMP;'
from dba_users where temporary_tablespace != 'TEMP';
SPOOL off
@c:\temp\all_tmp.sql

You could then save your dynamic SQL script to the Script Manager. You would then always have this query available.

Alternatively and without the need to spool out sql, try this :

begin
  for x in (select username
            from   dba_users
            where  temporary_tablespace != 'TEMP') loop
    execute immediate 'alter user '||x.username||' temporary tablespace temp';
  end loop;
end;

For closer SQL*Plus compatibility, TOAD 7.4 defaults the value of LINESIZE to 80. This means that unless you include a SET LINESIZE statement in your script, the maximum width of the script output will be set at 80. If you wish to override this default, version 7.4.0.3 allows you to add the following setting to your Toad.ini file, which allows you to set the default linesize to be applied to scripts in TOAD:

[SCRIPTS]
LineSize=512

The beta has an option under Options → Script Defaults so that you can set the default linesize without modifying the Toad.ini file directly.

This is the amount of time it takes for execution to return to the Sql Editor after sending the statement to Oracle. This will also include any time it takes to fetch the initial 25 rows. So, this is the amount of time it takes IN TOAD to execute the statement and is not meant to correspond to TKProf.

If the user doesn't have a license for Formatter Plus, they're not supposed to have the Code Statistics tab. In 7.2, we gave away the functionality by accident. In 7.3, we are hiding it again. In 7.4, it is shown.

If you have TOAD 7.2, this was a bug in that version. It is fixed in TOAD 7.3.

A query on the TOAD group from "trisibodo" asked why there was a difference between the formats used to display - and input - dates in the MOE when a statement was executed using F5 or F9. Norm replied as follows (blatant cut and paste) :

Ok, in the MOE (Mother Of all Editors) an SQL statement can be executed with F9 or run as a script with F5.

If you F5 to run as a script, the behaviour is as identical to that of SQL*Plus as is humanly possible, so the default date format is taken from NLS_DATE_FORMAT for your session. Hence the dates will be seen in whatever has been defined - or in the database's own default setting (DD-MON-YY I suspect.).

With F9 the dates are displayed in the format defined in VIEW→TOAD OPTIONS→DATA GRIDS→DATA then Date Format and Time Format. This is how dates are DISPLAYED. To enter a date you should still be wrapping strings in TO_DATE() with the correct format for the input string.

The fact that DD/MM/YYYY actually works without TOAD being told that that is the format is because Oracle database code is set up to try to interpret a date format as best it can - sometimes it works and other times it barfs. So a DD/MM/YYYY is easy - thanks to the separators, but your YYYYMMDD is confusing - that could easily be ddmmyyyy or even the ever popular mmddyyyy format - it depends on the numbers involved.

So you really need to be using to_date(whatever, 'yyyymmdd') to make sure that Oracle knows what you really want to say!

Of course I can use the "alter session set nls_date_format = 'yyyymmdd'" command, but I'd need to type and execute it each time I open a new sql window, and it's quite easy to forget it and ruin the following scripts. Is there any way to have it executed automatically, or to change the format in any other way?

VIEW→TOAD OPTIONS→STARTUP→File to load on startup and/or File to load on new connections.

Stick an ALTER SESSION in a file, and configure those two options (or one) with the file name and it will do it for you. Also, GLOGIN.SQL and LOGIN.SQL (according to your Oracle version) will also be executed from Oracle Home by TOAD as well.

Also, when executing commands as scripts (i.e., F5), the "glogin.sql" file, which has an "alter session set nls_date_format = 'yyyymmdd'", is executed, but the dates are shown as "yyyymmdd" (literally). Why does it happen?

See above. F5 runs as a clone of SQL*Plus. So the alter session changes the default manner in which dates are displayed and entered - if you don't use TO_DATE or TO_CHAR to specify a format, Oracle uses the default you have set for both. Hence displayed or entered dates show up in your chosen format of yyyymmdd - because you asked it to.

Updated for the Schema Browser

Steve Booth raised a problem with Toad 8.5.3.2 and date/time entry in the Schema Browser. He regularly has to correct or enter dates and times in the tables' data tab on the RHS of the SB. When doing so he was 'forced' to use a specific date format which was different from that set in his options, as follows :

I'm having some irritation where the date/time in the SB is displayed as I specify in the options, but when I edit the value, I must do so in the MM/DD/YYYY hh:mi:ss {AM/PM} format. Almost all of my work is done using the 24 hour clock.

John Dorlon provided the answer, the SB date format is taken from the Windows Regional Settings date and time format. Changing this to a 24 hour time format cured Steve's problem.

Two observations have been made on the with keyword and Toad 9.5 GA, these being :

1. The formatter does have with problems, but a new DLL has been available for a while to fix the problem. I don't think it is official yet, but widely used according to the beta list. Check the archives for details of where and how to get it.

2. The use of with in the MOE or in PL/SQL *is* supported by TOAD at least in version 9.5 onwards. I've used it for ages. However, make sure that you have an Oracle Home that is 9i or greater. It is not supported by Oracle Client previous to 9i.

This is why Toad can't support with if the client rejects it. If you find Toad can't handle with but SQL*Plus can, then check your settings because SQL*Plus is using a different and more recent client that Toad is. The 'new session' dialogue is a good place to look.

What's with I hear you ask?

WITH fred AS (SELECT object_name, object_type FROM user_objects)
SELECT * FROM fred 
WHERE rownum < 5;

The result of the above extremely simple example are :

This is a slightly bigger example and shows the worth of with a little better. It allows the developer to extract the work of the various sub-queries from the main query, making it better to read, maintain and understand.

WITH total_space AS
    (SELECT tablespace_name, 
            sum(bytes)/1024/1024 AS total_mb,
            count(*) AS total_files
     FROM   dba_data_files
     GROUP  BY tablespace_name) ,
--
     free_space AS
     (SELECT tablespace_name,
             sum(bytes)/1024/1024 AS free_mb,
             count(*) AS free_fragments
      FROM   dba_free_space
      GROUP  BY tablespace_name)
--
SELECT s.tablespace_name,
       s.total_files AS num_files,
       round(s.total_mb,2) AS total_mb,
       round(f.free_mb,2) AS free_mb,
       f.free_fragments
FROM   total_space s,
       free_space f
WHERE  s.tablespace_name = f.tablespace_name(+)
ORDER BY s.tablespace_name;

On my small test system we get this :

Stop press it has been mentioned on the list, by Roger Simoneau, that Toad 8.6.1 alongside an Oracle 10g client, can handle the with statement quite happily.

One from Wendy Penfold on the Toad lists:

Hi, I need to load a large file of insert statements–44,000. The file is too large to open in the Toad editor, it hangs Toad. Is there a way I can run the script without opening it in the editor? Thanks! Wendy

A number of replies were received from the developers guys, and they all said very much the same thing :

• In the MOE, simply type @c:\path\to\script\script_name.sql
• then press F5.

Alternatively, it can be run in QSR (Quest Script Runner) using exactly the same command.

Yes. See the TOAD help file for a list of which source control providers have been tested with TOAD through the SCC API and which ones have been tested with Team Coding in TOAD.

If you are using TOAD 7.4 or earlier, it's likely that TOAD's parser has found the wrong name for your procedure. This will happen if your procedure's name is a word that TOAD recognizes for syntax highlighting or if you have another object by the same name as your procedure. The list of words that TOAD uses for syntax highlighting is read from plsqlscr.txt in your \User Files directory. We recommend upgrading to TOAD 7.5 or higher, as the procedure editor is now using a completely different parsing routine.

Is there a particular statement that is causing the timeout? If so, there is a debug timeout value in the debugger page of the options screen. Make sure it is sufficiently high enough to allow a server response from whatever statement is being run on the server. If this doesn't appear to be the problem then make sure the anonymous block that is being run is valid (copy it to clipboard and try running it in the SQL window).

Check to see whether you have an object named the same as the owner of the procedure. When this is the case, Oracle thinks that the user name in the anonymous block is actually the object name. We recommend either renaming the problematic object or manually removing the user name from the anonymous block in the Set Parameters window.

Set the following options in TOAD:

1. View→Options→Procedure Editor→Only show one Procedure Editor per database connection
2. View→Options→Debugging→Compile Dependencies to YES or PROMPT
3. View→Options→Debugging→Debug session timeout and increase debug session timeout to 300 (increase to large value if you're expecting long fetch during debugging).

3 possible reasons:

1. You need to have a license key with the Debugger enabled.
2. You need to have DBMS_DEBUG installed on your database. Please see the help file.
3. If your server is version 10g, then your session needs to have the "DEBUG CONNECT SESSION" system privilege.

The problem is that the code that is run when debugging is what is stored on the server - not the script. When you have a comment block BEFORE the CREATE statement, it is stripped out and not stored on the server. Therefore the code that is visible on the screen is NOT the code that is running. Load the object from the database and you will see what I mean. If a user wants to preserve the comment block AND have it work correctly with the debugger, then they need to move the comment block to BELOW the CREATE statement.

The reason was an AFTER SERVERERROR trigger. This trigger writes the last SQL statement, the error message, user and systime into an error log table. Somehow this trigger conflicts with Oracle's/TOAD's debugging mechanism. When we disable the trigger, everything works fine. This is a solution, because on the development machine, where debugging is mostly done, we don't really need this error logging. On the production machine, debugging is rare, so the trigger can stay enabled.

(Contributed by Thomas B)

When using the PL/SQL Debugger with a RAC database, you must either have an additional entry in your TNSNames.ora file for the connected instance, or you must connect directly to an instance of the cluster without letting the server assign an instance.

During debugging, Toad creates two background sessions for handling debugger calls, called the Target and Debug sessions. These two sessions must be created on the same instance as the main Toad session, because Oracle does not support Debugging across RAC instances.

To accomplish this, Toad queries the data dictionary to find the current instance name, then searches the TNSNames.ora file, starting at the beginning, for the first entry which has either the SERVICE_NAME or INSTANCE_NAME equal to the name found in the data dictionary. Toad will then use this secondary TNSNames entry when creating the background debugger sessions.

For example, in a two node RAC environment, a user would have their main RAC entry looking something like this:

RAC =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.2)(PORT = 1521))            
      )
      (LOAD_BALANCE = ON)
      (CONNECT_DATA =
          (SERVICE_NAME = RAC)
          (FAILOVER_MODE = 
      (TYPE = SELECT)
      (METHOD = BASIC)
      (RETRIES = 180)
            (DELAY = 5)
          )
      )
  )

In this two node environment, the user would then need to have two additional TNSNames entries, one each for the individual instances in the RAC. In these entries, the can either use the syntax:

RAC1 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = RAC)       # notice the difference
          (INSTANCE_NAME = RAC1)     # betwen this
      )
  )

Or:

RAC2 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.2)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = RAC2)      # and this
      )
  )