TOAD issues / bugs (v11-12)

Toad for Oracle
1

Here’s a list of issues in TOAD v11 to v12.

1. PL/SQL Condition Defines, breaks navigator.

Embedded conditional defines in statements break TOAD’s navigator, making it totally useless, see screenshot below. Function InsertOrUpdateChildXML has a conditional define to ensure Oracle does not use deprecated method ExistsNode on 11gR2. AddNodeIfNotExists and Test2 (and all other subsequent procs) do not appear in navigator. Having a broken navigator makes developing large packages extremely difficult.

[View:http://i.imgur.com/ITkjTTk.jpg:550:0]

2. Syntax Highlighter - Missing functionality

a) No syntax highlighting for PL/SQL Condition Defines.

An option to highlight conditional defines should exist in Options | Editor | Behaviour | Languages (PL/SQL) | Syntax Highlighting. I probably could add one myself with a regular expression, but it should be part of the editor defaults.

**b) No option to change DBMS_Output “Marked Block”. **

I changed my default background to blue to avoid eye glare (Options | Editor | Display | Background Color). But can now no longer see selections in DBMS_Output window (if I want to select a block and copy to clipboard), and there seems to be no option to change it.

3. Master-Detail Browser

a) No option to product SQL for Master-Detail Browser record(s).

The Master-Detail Browser is very useful, but its use is diminished by lacking a facility to generate SQL for a selected record in one of the grids. e.g. I add a Parent -> Child -> Grandchild relationship. If I go to one of the grids, it would be nice to be able to generate the SQL for the record in question in that grid. For example, in the grandchild region, have buttons for generating SQL for record and generate SQL for grid. E.g. For record in grandchild grid…

select gc.*

from GRANCHILD gc

join CHILD c on c.ID = gc.PARENT_ID

join PARENT p on p.ID = c.PARENT_ID

where gc.ID = 123

�

b) No option to clear everything to create a new set of relationships.

There should be a button “Create New Master-Detail relationship”

�

4. Default Background Colour not honoured in many sub forms.

I set my background colour to blue, but lots of places still show source code on a white background and due to font colours scheme, I can no longer see the code. What’s the point of having default colours / fonts if it’s ignored?

a) Substitution variables prompt dialog => run query with variables.

b) Options | Editor | Behaviour | Languages | General => preview colour highlighting

2

Hi PaulZip,

As to “PL/SQL Condition Defines, breaks navigator” could you please paste your code here in text form so that we can try it out without having to type it over?

Thanks,

Andre

3

Simplest demo… But any conditional define breaks it.

create or replace package P_Test is

function BreakNavigator return boolean is
begin
select
$IF DBMS_DB_VERSION.VERSION >= 11 then
‘Eleven’
$ELSE
‘Not eleven’
$END
from dual;
end;

function BrokenAndNotInNavigator return boolean is
begin
return TRUE;
end;

function BrokenAndNotInNavigatorToo return boolean is
begin
return TRUE;
end;

end;

4

I meant…

create or replace package body P_Test is

5

Sorry, that was rushed with some syntax issues. Here you go…

create or replace package body P_Test is

function BreakNavigator return varchar2 is

vResult varchar2(20);

begin

select

$IF DBMS_DB_VERSION.VERSION >= 11 then

‘Eleven’

$ELSE

‘Not eleven’

$END

into vResult

from dual;

return vResult;

end;

function BrokenAndNotInNavigator return boolean is

begin

return TRUE;

end;

function BrokenAndNotInNavigatorToo return boolean is

begin

return TRUE;

end;

end;

6

Thanks. The navigator will work better if you write $THEN instead of THEN. I admit that the error message on $IF is misleading when you write THEN without $, we’ll try to fix that.

7

Yes, the $then was a typo, but there is still a problem with the parser and its interaction with the navigator. Try this…

create or replace package body P_Test is

function InsertOrUpdateChildXML(pXMLData XMLType, pXPath varchar2, pChildNodeName varchar2, pChildValue varchar2) return XMLType is

vParentXPath varchar2(1000) := pXPath||’/’||pChildNodeName;

vResult XMLType;

begin

select

case when

$IF DBMS_DB_VERSION.VERSION >= 11 and DBMS_DB_VERSION.RELEASE >=2 $THEN – In 11gR2 XMLExists is recommended

 XMLExists(vParentXPath passing pXMLData)

$ELSE

 ExistsNode(pXMLData, vParentXPath) = 1 then

$END

UpdateXML(pXMLDATA, vParentXPath, XMLNode)

else InsertChildXML(pXMLData, pXPath, pChildNodeName, XMLNode)

end as NEWXMLDATA

into vResult

from (select XMLElement(Evalname pChildNodeName, pChildValue) as XMLNode

   from   dual);

return vResult;

end;

function BrokenAndNotInNavigator return boolean is

begin

return TRUE;

end;

function BrokenAndNotInNavigatorToo return boolean is

begin

return TRUE;

end;

end;

8

$IF …

XMLExists(vParentXPath passing pXMLData) THEN

$ELSE

…

will show the other functions.

9

Perfect, many thanks.

10

You’re welcome.