AskToad.com -- Home of your Toad Oracle KnowledgeBase

Differences

This shows you the differences between the selected revision and the current version of the page.

faq:answers:sql_editor 2009/01/08 14:17 faq:answers:sql_editor 2009/07/22 18:08 current
Line 1: Line 1:
-====== SQL_Editor ====== +====== SQL Editor ======
- +
- +
-===== How can I use TOAD to create and run dynamic SQL? ===== +
- +
-<code> +
-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 +
-</code> +
-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 : +
- +
-<code> +
-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; +
-</code> +
- +
-===== My script output is truncating at 80 characters. How do I fix this? ===== +
- +
-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: +
-<code> +
-[SCRIPTS] +
-LineSize=512 +
-</code> +
-The beta has an option under Options -> Script Defaults so +
-that you can set the default linesize without modifying the Toad.ini +
-file directly. +
- +
-===== What does the execution time in the sql editor actually represent? What I'm trying to pin down is how accurate the execution time displayed in TOAD is. For some complex queries, the time displayed may be 1 second. If I trace the query using TKPROF it reports about 4 seconds. I assume TOAD is populating the grid as fast as it can and continuing to run the query in the background ? ===== +
- +
-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. +
- +
-===== When I upgraded from 7.2 to 7.3, my Code Statistics tab disappeared in the SQL Editor. What's going on? ===== +
- +
-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. +
- +
-===== Why are tabs no longer recognized as valid whitespace in a query? ===== +
- +
-If you have TOAD 7.2, this was a bug in that version. It is fixed in TOAD 7.3. +
- +
- +
-===== What's the deal with dates? ===== +
- +
-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 (**M**other **O**f all **E**ditors) 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. +
- +
- +
- +
- +
- +
-===== What's With With? ===== +
- +
-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? +
- +
-<code sql> +
-WITH fred as (select object_name, object_type from user_objects) +
-Select * from fred  +
-where rownum < 5; +
-</code> +
-The result of the above extremely simple example are : +
- +
-^OBJECT_NAME^OBJECT_TYPE^ +
-|EMPLOYEES|TABLE| +
-|BETWNSTR|FUNCTION| +
-|CHECKPASSWORDCOMPLEXITY|PROCEDURE| +
-|PKPSWDUSER|PACKAGE| +
- +
- +
-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. +
- +
-<code sql> +
-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; +
-</code> +
- +
-On my small test system we get this : +
- +
-^TABLESPACE_NAME^NUM_FILES^TOTAL_MB^FREE_MB^FREE_FRAGMENTS^ +
-|CTX|  1|  51|  46.19|  1| +
-|NORMAN|  1|  51|  46.69|  6| +
-|PERFSTAT|  1|  501|  189.25|  25| +
-|SYSTEM|  1|  251|  76.38|  2| +
-|UNDO|  1|  601|  585.06|  3| +
-|WAQS|  1|  51|  49.56|  2| +
- +
-//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.  +
- +
-===== How Do I Run Very Large Scripts ===== +
- +
-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. +
 +This page of FAQ answers has been merged into the [[faq:answers:editor|Editor]] page.
 + --- //[[contact|Rich]] 2009/07/22 18:04//


Personal tools
navigation

11/30/09