DataGrid

TOAD 7.5 and up can show TIMESTAMP and INTERVAL data in its data grids if you are using a 9.0.1 or higher Oracle client. TOAD 9.0.1 supports TIMESTAMP and INTERVAL data in its "run as script" function. TOAD versions 7.4 and previous do not support TIMESTAMP and INTERVAL data due to a limitation in their data layer.

To see timestamp/intervals in the "datatype" drop down in the Create/Alter Table window, and in the "Add Column" window, go to options → datatypes, and make sure "Include Timestamp/Interval Types" is checked.

TOAD 7.5 and up can show nested object type data in popups from the data grids if you are using an 8.0 or higher Oracle client. Right-click on the cell that should contain the data and choose "Popup editor". TOAD 9.0.1 now supports nested object data in its "run as script" function. TOAD versions 7.4 and previous do not support nested object data due to a limitation in their data layer.

TOAD 7.5 and up can show nested table data in popups from the data grids if you are using an 8.0 or higher Oracle client. The nested table data will appear in the grid cell as "(DATASET)". Right-click over this cell and choose "Popup editor". TOAD 9.0.1 now supports nested table data in its "run as script" function. TOAD versions 7.4 and previous do not support nested table data due to a limitation in their data layer.

Reset NLS_LANG on your Client machine to AMERICAN_AMERICA.JA16SJIS to view Japanese and AMERICAN_AMERICA.ZHT16BIG5 to view Chinese. Then change the font script for each language by right clicking on the data and choosing Grid Options | Data Grids - Visual | Fonts | Grid and choose Arial Unicode MS as the Font. This changes the script to Japanese for Japanese characters or to ChineseBig5 for Chinese.

To copy one row of data, put your cursor in a cell in the grid and press Ctrl+Insert. This will copy the row and its column headers to the clipboard. To copy multiple rows of data, right-click in the grid and choose "Allow multiselect". Use Shift-Click to select multiple adjacent rows, or use Ctrl+Click to select individual, non-adjacent rows. Press Ctrl+C. This will copy all of the rows and their column headers to the clipboard. To copy a single cell's data, turn off "Allow multiselect". Click on the cell you want to copy and press Ctrl+C.

The following table gives details of what happens with the various copy keys and multi-select options.

Assuming that the result set is editable (i.e. you have written a query that includes the ROWID, or you have used the TOAD Edit command), you can do one of the following:

• "Delete row" button on the SQL Editor toolbar
• CTRL+Del
• Single Record View, Delete Button

We have found that this happens when the database character set is WE8ISO8859P1, and the Oracle client is 9.2.0.1. This is an Oracle bug, and upgrading your client to 9.2.0.4 or higher should resolve the problem. In any other case, please generate a TOAD Support Bundle from the Help menu and let us know what you are seeing. Thanks!

When "Clone Cursor" is turned OFF, and a user goes to the "save as" screen and begins an export, we use the actual cursor which is tied to the data grid. The advantage to this is that the query does not need to be re-executed. The disadvantage to this is that the whole dataset must be held in your PC's RAM, because this is a scrollable dataset (it is this mechanism that allows scrolling in the grids). When "Clone Cursor" is turned ON, and a user goes to the "save as" screen and begins an export, we create a new, non-scrollable cursor. The advantage here is that as rows are read in and sent to the destination file, we don't have to hold them in memory any longer...and a minimal amout of RAM is used. The disadvantage is that for this to happen, the query must be re-executed. So...which setting should you use? If your query returns a LOT of rows (too many to hold in your PC's RAM), you should have "Clone Cursor" turned ON...even if your query takes a long time to execute. If your query returns a number of rows such that the entire dataset will easily fit into your PC's memory, then we should consider the execution time for the query. If execution time is slow, then leave "Clone Cursor" OFF. If execution time is fast, then it doesn't really matter.

This option has been renamed to "Display all exported results in grid" for version 9.1. It will be greyed out if the dataset being exported has already been completely fetched, e.g. OCI Array = 500 and only 125 records, or you have already scolled to the bottom of the result set. If either of these cases are not true, then if this option IS set, Toad will execute the statement again and fetch ALL of the rows for the export, and then also send the entire result set to the original data grid so you can see them there too after the export has finished.

After a SELECT query is executed, we must retrieve the rows from the Oracle server to your PC. We do not retrieve the rows all at once, nor do we retrieve them one at a time (unless there is a LONG or LOB column involved). We retrieve the rows in blocks. The number of rows retrieved in each block is the number of rows you specify with "OCI Array Buffer Size". TOAD defaults to 25 because SQL*Plus defaults to 25. In my opinion, an optimal setting is more like 500 or 1000. Here is why: If your dataset has 1,000 rows, and your OCI Array Buffer size is set to 25, then TOAD has to make 40 (that's 1000 / 25) round trips across the network to retrieve all of the rows. So you can immediately see why 500 or 1000 is better. The only disadvantage to a higher setting of OCI Array Buffer Size is that TOAD must allocate memory to hold that many rows prior to each fetch. If that many rows are actually fetched, there is no loss. On the other hand, if not that many rows are retrieved, then we allocated some memory that is not going to be released until the cursor is freed. Luckily, this is a trivial amout of memory, in the grand scheme of things.

Please upgrade your copy of TOAD to at least version 7.4.0.3. This functionality has been restored.

In older versions of TOAD, the grid is limited to adding/modifying CLOB's for existing records. This should be working in TOAD 7.6. Note, however, that since TOAD does not support Unicode databases, that there are often errors when viewing CLOB data in the data grids for databases with Unicode character sets.

Query statements MUST return the ROWID to be updatable. For example:

select * from employee where salary > 2000

would not be updatable, whereas:

select employee.*, rowid from employee where salary > 2000

would be updatable.

To reduce this obvious nuisance, you can use the TOAD-specific EDIT statement. For example:

edit employee where salary > 2000

If the resultset should be editable but remains read-only, make sure the TOAD Options > Data Grids - Data tab, Default to Read-Only Queries checkbox is NOT enabled

Try upgrading to the current release of TOAD, as we have obtained an updated XLS component. If you are still experiencing the problem, you can do this: FORMAT → CELLS → NUMBER TAB → change to Currency, Percentage, etc.

From the Toad lists.

One of my co-workers experiences a very strange problem. When he is saving the results of a query using "Save As" into Excel Instance nothing happens. There is 1 opened Excel and it stays empty. All other "save as" option work fine (including saving into Xls File). What could be the problem?

John Dorlon replied :

• Close excel.
• Look in task manager.
• If you still see excel running, kill it.

It seems that Excel gets confused as to whether it is running once or not and the hidden instance of Excel is causing the problem as it is most likely receiving the saved data. (explanation courtesy of Norm and it might even be right! )

When you run a script in the MOE using F5, the output of the script is displayed at the bottom of the screen. Each command creates a separate tab for its results, as well as being included in the main script output tab. These tabs are simply named 'Grid n' where n is an incrementing number starting at 1. If you run a script with 5 SELECT statements, you get 'Grid 1' though 'Grid 5'.

A question on the list from Roger Simoneau, asked how we could programatically rename the tabs from within the script. Both Erwin Rollauer and Ed Klinger (TeamT) supplied answers.

If you use the TTITLE directive before the SELECT statement, the tab will be named using the data from the TTITLE directive, as follows :

TTITLE today
SELECT sysdate FROM dual;

The tab for this statement will have the title 'today' exactly as entered.

If you need to use more than one word for the tab name, you must wrap the words in quotes, single ( ' ) or double ( " ) work equally as well.

TTITLE "today is roughly around ..."
SELECT sysdate FROM dual;

Try it and see. I wonder if TTITLE actually stands for 'Tab Title'? .

Another one from the list. Herald asked > I changed the font for the DATA Grids, so I could look at Unicode. Now I see the chosen font in the datagrid, but if I open the popup editor it still gives the old font. Is it possible to change this font also, I didn't find it in the options, or could you make the grid popup editor font the same as the data grid font.

After much searching, Brad supplied the following obvious solution :

• Go to View → Toad Options → Editor → Behavior.
• In the Languages section, select "Plain Text" from the dropdown and click the Edit ... button next to the dropdown.
• Go to the Highlighting tab and click the "custom font" button.
• Choose your font and OK your way out.