faq:answers:editor

AskToad.com -- Home of your Toad Oracle KnowledgeBase

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Editor ======


===== How to change the editor font in Toad 9.0? ======

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

===== How do the Editor execution buttons in Toad 9.0 compare with those of version 8.6? =====

For a more comprehensive review of all the new Editor features, click [[http://www.toadsoft.com/get2know9/#Moe1|here]]
{{:faq:answers:use_matrix2.jpg|:faq:answers:use_matrix2.jpg}}

===== "Format Code" isn't working. It corrupts my code, or it doesn't use any of my Formatting Options. What's going on? =====

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:
  - Open Formatter Plus and enter your Formatter license key.
  - Restart TOAD.

===== "Load in External Editor" isn't doing anything. =====

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.

===== How do I set editor options for the memo editor [text editor]? For example, I would like to set the word wrapping options... =====

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:
  - Open the memo editor with text that isn't HTML or XML (because those use the html parser)
  - Right-click and choose Options -> Editing options
  - Under General options, choose to word-wrap text
  - Under Display options, choose to show word-wrapped lines with the |> glyph
  - 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. 
  - Press OK.


===== How do i configure TOAD to highlight multi-line strings? =====

Look under Options -> Parser Scripts for the location of your parser
script, probably \User Files\plsqlscr.txt. In that parser script, look under
"<nowiki>%%</nowiki>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 "<nowiki>//</nowiki>" is a comment marker.\\
'text in single quotes'\\
"text in doubled quotes"\\

  * <nowiki>__</nowiki>STD_PASCALSTRING: 1st line red, Other 3 not
  * <nowiki>__</nowiki>STD_PLSQLSTRING: 1st 2 lines red, Next 2 not
  * <nowiki>__</nowiki>STD_C_STRING: 3rd line red, Other 3 not

  * <nowiki>__</nowiki>STD_PASCALSTRING and <nowiki>__</nowiki>STD_C_STRING: 1st line and 3rd line red.  Other 2 not.
  * <nowiki>__</nowiki>STD_PLSQLSTRING and <nowiki>__</nowiki>STD_C_STRING: 1st, 2nd, and 3rd line red.  4th line not.
  * <nowiki>__</nowiki>STD_PASCALSTRING and <nowiki>__</nowiki>STD_PLSQLSTRING: Parser error when starting TOAD.

==== Summary ====

  * <nowiki>__</nowiki>STD_C_STRING tells the parser that text on a single line between double-quotes is a string.
  * <nowiki>__</nowiki>STD_PASCALSTRING tells the parser that text on a single line between single-quotes is a string.
  * <nowiki>__</nowiki>STD_PLSQLSTRING tells the parser that any text (multiple lines or not) between single-quotes is a string.
  * <nowiki>__</nowiki>STD_PLSQLSTRING and <nowiki>__</nowiki>STD_PASCALSTRING are incompatible, but either can be used in conjunction with <nowiki>__</nowiki>STD_C_STRING.

  * The default setting enables <nowiki>__</nowiki>STD_PASCALSTRING and <nowiki>__</nowiki>STD_C_STRING

==== Caveats ====

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 <nowiki>__</nowiki>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:
<code>
a := 'my string';
c := 'my table';
</code>

Now suppose you are inserting a line, like so:

<code>
a := 'my string';
b := '
c := 'my table';
</code>

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:

<code>
a := 'my string';
b := 'my other string';
c := 'my TABLE';
</code>

===== How do you jump to a previously defined bookmark in SQL Edit/Procedure Edit windows? =====

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 + #

===== I'm having problems with tab stops. They're not consistent, and when I open a file created in TOAD in another editor, the spacing is all uneven. =====

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.


===== My clipboard keys (cut Ctrl+X, copy Ctrl+C, paste Ctrl+V) don't work. What happened? =====

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: 
  - Close TOAD.
  - Delete "plsqlkeys.bin" from your \User Files directory.
  - 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.


===== The backspace, up, and down arrow keys don't work in my TOAD. What's going on? =====

We suspect that the file "plsqlkeys.bin" is getting corrupted. Please
try the following:
  - Close TOAD.
  - Delete "plsqlkeys.bin" from your \User Files directory.
  - Reopen TOAD and see whether those keys now work.

===== When I click in the editor, I keep getting the message "Object xxx not found". =====

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.

===== When I describe a table (say hit F4 on the table name in a SQL query) I often like to copy and paste Column Names from the Columns tab into a SQL statement - however when i do paste it,  the column name, id, type etc get pasted as well. Is there any option to revert to the old way where the column name was simply copied without the datatype etc.?  I know i can drag and drop it, but I'd prefer to copy and paste it. =====

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.

===== How do I disable the block indicator lines in the editor? ===== 


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.


===== How do I search for special characters (metacharacters) in the editor ===== 


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.
\\
^ Type this ^ To search for this ^
|%%\r%%| Carriage return |
|%%\n%%| New line (Line feed) |
|%%\f%%| Form feed (page feed) |
|%%\t%%| Tab character |
|%%\b%%| Backspace |
|%%\s%%| Whitespace character |
|%%\S%%| Space |
|%%.%% (a dot)| Any single character. For example, %%c.b%% matches cab, cob, and cub.|
|%%[]%%| Any one of the characters in the brackets, or any range of characters separated by a hyphen, or a character class operator.|
|%%[^]%%| Any character that EXCEPTING those after the caret. For example, %%c[^u]b%% will match cab and cob, but not cub.|
|%%^%%| Start of a line.|
|%%$%%| End of a line (but not the line break characters)|
|%%*%%| Matches none or more of the preceding characters or expressions. For example, %%bo*t%% matches bt, bot, and boot.|
|%%?%%| Matches zero or one of the preceding characters or expressions. For example, %%bo?t%% matches bt, bot, but not boot.|
|%%+%%| Matches one or more of the preceding characters or expressions. For example, %%bo+t%% matches bot, and boot but not bt.|
\\
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

===== Why are strings in my code no longer highlighted in red in TOAD 9.1? ======

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.

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


===== Does TOAD support VSS Microsoft Visual Source Safe, version 5 and version 6? =====

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.


===== Every time I try to step into my procedure, it opens another tab. What is going on? =====

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.

===== I have a developer trying to debug using TOAD. He is getting a "probe timeout" error. The debug package is loaded correctly. What should I look at to fix the issue?? =====

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

===== When I try to run my procedure through the debugger, I get an error that says "PLS-00201: identifier ... must be declared". =====

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.

===== When debugging (stepping through an object, set break points,) a package, procedure, or function, TOAD displays "debugger is not responding" or TOAD does not respond and the user has to exit TOAD by CTRL + ALT and Delete to exit the application. =====

Set the following options in TOAD:
  - View->Options->Procedure Editor->Only show one Procedure Editor per database connection
  - View->Options->Debugging->Compile Dependencies to YES or PROMPT
  - 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).

===== Why is the bug icon and my debugger toolbar disabled? =====

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

===== Why is the debugger highlighting the wrong code lines when debugging my package? It looks like it is going through the comment lines. =====

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.

===== In the debugger, when stepping through code with Shift-F7/F8 and then halting the execution, TOAD does not respond any more =====

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)//

===== How do I debug PL/SQL when connected to a RAC? =====

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
        )
    )
AskToad.com -- Home of your Toad Oracle KnowledgeBase

Personal tools

Views

navigation

search
