Toad Parser Bug

Toad for Oracle & DevOps Toolkit
1

Toad 16.1.53.1594 has a parser bug, which prevents the code Navigator working correctly, if conditional compilation is used. Example below.

create or replace package body P_Test as

$IF DBMS_DB_VERSION.VER_LE_11 $THEN
VAL constant boolean := True;
$ELSIF DBMS_DB_VERSION.VER_LE_12_1 $THEN
VAL constant boolean := False;
$ELSE

procedure Test is
-- This won't appear in the navigator, nor will anything else in this section.
begin
null;
end;

end;
/

2

I'm missing a $END.
Perhaps the $ELSE was supposed to be $END.

3

Sorry, yes there should be an $END

In my cases I have the $END, but I'm not seeing navigator content, lots of code folded for brevity.

image
image820×237 14 KB

4

Traced the problem, in my scenario, it seems to be if a column called KEY is referred to in a decode and it isn't aliased. Probably a reserved word issue, even though compiles fine.

create or replace package body P_Test as

$IF DBMS_DB_VERSION.VER_LE_11 $THEN
VAL constant boolean := True;
$ELSIF DBMS_DB_VERSION.VER_LE_12_1 $THEN
VAL constant boolean := False;
$ELSE

procedure Test is
-- This won't appear in the navigator, nor will anything else in this section.
  vVal ATABLE.KEY_OTHER%TYPE;
begin
  select max(KEY_OTHER) keep (dense_rank first order by PREFERENCE_ORDER, decode(KEY, 'SOMETHING', 1, 2))
  into vVal
  from  ATABLE
  where COL1 = 1;
  dbms_output.put_line(vVal);
end;

$END
end;
/
5

The parser only takes the $IF branch into account. Internally it comments out $ELSIF and $ELSE code.

Consider the following example:

    VAL constant boolean := 
$IF DBMS_DB_VERSION.VER_LE_11 $THEN
         True;
$ELSIF DBMS_DB_VERSION.VER_LE_12_1 $THEN
         False;
$ELSE

Code inside conditional compilation directives can be anything, so a choice between $IF and $ELSE/$ELSIF has to be made to keep the code valid. We just can't concatenate both clauses.
Preserving the $IF code is the choice we made.
But as always, suggestions welcome!

Andre

6

Hi Andre,

This conditional compilation version check nesting is a fairly standard approach, Oracle use it themselves, it's because DBMS_DB_VERSION only defines certain VER_LE_* constants with a particular version, if you don't check the lower versions first it'll fail that way. The constants between the sections were to work around another parser bug here, so not really that important.

This package uses aspects that are only available in 12.2 but only certain customers of ours (the ones on 12.2 or higher) use it, so it allows us a to create a single vanilla build script.

I don't know the feasibility of this as a suggestion, but how about either :
a) If you come across a conditional compilation DBMS_DB_VERSION check, parse out the constants from the schema's SYS.DBMS_DB_VERSION package spec (once) and so you can evaluate them properly.
b) Detecting the schema's version on login and the parser handling DBMS_DB_VERSION.VER* checks based off that, so that the correct branch in and $if,$elsif....,$else are considered?

Also, what are your thoughts on the "KEY" column name issue?

Regards
Paul

7

Hi Paul,

Evaluating the DBMS_DB_VERSION constants and selecting the right branches is part of some version related feature in our pipeline. Can't tell anything about availability though. But I'm glad you're suggesting it.

The KEY keyword is causing a freaking strange behavior :astonished: , I'll look into this asap.

Thanks!
Andre

1 Like
8

Hello Paul,

I think I told you only half of the truth. Here's an excerpt of our parsing of conditional compilation parsing doc:

The Oracle parser will parse conditional compilation directives as follows:

  • In a first pass, it will parse $IF/$ELSIF/$ELSE/$END constructs assuming that they are "structured" and that a syntax tree can be built containing both $THEN and $ELSE branches. QP5 tries to create parse info by treating these branches (perhaps declarations or statements) as nodes, just like regular statements, so that a parse info tree can be built reflecting all IF/ELSE branches.
    Sometimes this is not possible because the $-directives can include/exclude any portion of text.
    The cases where "structured code" is recognized are described below.

  • If the above parse yields errors, probably due to the presence of conditional compilation directives and $IF/$ELSE clauses which are not at all symmetrical, then a new second pass parse (fallback parse) will be attempted with the assumption that all $IF predicates have the value TRUE, and all $ELSIF/$ELSE branches are FALSE. The $IF...$THEN span (the actual condition, that is) and all $ELSIF...$END or $ELSE...$END spans will then be turned into comments. This means that a syntax check will only check the code in the $IF branch, between the $THEN and the next $ish token.

Structured parsing of conditional compilation directives is desirable for both syntax checking and formatting as both the $IF and $ELSE branches are checked and formatted. However it requires the user to follow a few rules.

The fallback parsing seems better for several kinds of analysis, as only the selected $IF/$ELSE branches are taken into account. And it works for all code.

By "structured" code we mean the following (unimplemented cases omitted):

Case 1 : conditional IF-THEN-ELSIF-ELSE statement

BEGIN
    $IF ...
    $THEN
        -- SQL statements or $ERROR ... $END
    $ELSIF ...
    $THEN
        -- SQL statements or $ERROR ... $END
    $ELSE
        -- SQL statements or $ERROR ... $END
    $END
END;

Case 2: conditional data type

DECLARE
    a   $IF ...
        $THEN
            -- data type or $ERROR ... $END
        $ELSIF ...
        $THEN
            -- data type or $ERROR ... $END
        $ELSE
            -- data type or $ERROR ... $END
        $END ;
BEGIN
    ...
END;

Case 4: conditional parameter declaration

CREATE PROCEDURE p (
        $IF ...
        $THEN
            -- parameter declaration or $ERROR ... $END
            -- e.g.   var1 IN NUMBER
        $ELSIF ...
        $THEN
            -- parameter declaration or $ERROR ... $END
        $ELSE
            -- parameter declaration or $ERROR ... $END
        $END
    ,
    var2 ....
)
IS
    ...

Case 7: conditional sets of declarations including the semicolon

DECLARE
$IF DBMS_DB_VERSION.VERSION >= 10
$THEN
    a NUMBER;
$ELSE
    a BINARY_FLOAT;
$END
    x NUMBER;
BEGIN
    NULL;
END;
 
DECLARE
$IF DBMS_DB_VERSION.VERSION >= 10
$THEN
    a NUMBER;
    b NUMBER;
$ELSE
    a BINARY_FLOAT;
    b BINARY_FLOAT;
$END
    x NUMBER;
BEGIN
    NULL;
END;

Case 8: conditional single declaration not including the semicolon

DECLARE
  $IF DBMS_DB_VERSION.VERSION >= 10
  $THEN
    a NUMBER
  $ELSE
    a BINARY_FLOAT
  $END
  ;
BEGIN
    NULL;
END;

So, in short, I see (should see) this:

image

and (CHANGING KEY BY KEY123 !)

image
image1164×400 25.7 KB

But when using KEY then it's broken -- we already know that:

image
image1161×418 23.2 KB

So again, up to me to look what that KEY is doing. :slight_smile:

Hope this helps for now,
Andre

1 Like
9

This snippet

SELECT f(KEY) FROM mytable;

yields a syntax error due to KEY. That is exactly the reason why your code looks okay and no error shows up: the conditional compilation logic falls back (second parse) to commenting out everything but the first $IF clause.

That issue with keywords was already listed in our queue. Fixing it will require quite a bit of work though, because it goes quite deep in the code.
The best workaround for now is renaming KEY, sorry.

Thanks,
Andre