annoying whitespace bug in Toad for Oracle 12.9 giving syntax error on unioned queries that run fine in 12.8(and previous versions)

Toad for Oracle & DevOps Toolkit
1

Hello,

I have encountered an annoying issue after upgrading my Toad For Oracle to 12.9 from 12.8. I have solved the issue but if the query runs as is in native sqlplus and in previous versions it should run here too. I posting this for Toad support and other sql coders that might need to fix similar queries or code similar statements.

I have a unioned query similar to the simplified version below. I simply go out to several databases through database links and look for employees that might exist in each.

I give each column a title and wrap the all the union query with a “select” using the titles of each column. I do this so I can add filters to the where clause like what you see below.

That way I can apply additional criteria to each set of database results without adding to each database’s where clause. I have five unioned queries so this format saves me a lot of time.

I have been running this query for years and it still runs in 12.8.

The syntax error Toad gives in 12.9 is in the blank line before the first union statement is "expecting -or- WITH -or- Fetch Offset -or- ORDER -or Intersect Minus Union…

The solution is to remove ALL blank lines in the unioned statements between the parens and even the additional where clause following the closing paren of the unioned statements.

I like to use the blank line to better visually isolate each query for maintaining. Also whitespace and blank lines are ANSIi sql compliant and should work without modification.

I would hope that Toad Development confirms and provides a patch that remedies this “false” syntax error.

select “NAME”, “DEPT”
from
(select emp_name “NAME”, emp_dept “DEPT”
from SCHEMA1.employee
where upper(emp_name) like ‘%&&NAME%’

union

select emp_name “NAME”, emp_dept “DEPT”
from SCHEMA1.employee@DB_LINK
where upper(emp_name) like ‘%&&NAME%’
)
where 1 = 1
and “DEPT” not in (‘TEMPLATE’)

2

There is a new option for 12.9 that defaults to enabled that you need to disable. Uncheck “Treat blank line as statement terminator” on the Execute/Compile page in Options.

The option allows for lazy programming where you can have gibberish and code lacking proper terminators in the Editor and still allow features like Code Insight, etc. to function. It’s sad that it had to come to this.

Thanks,

Michael

3

Hi Michael, That was the issue. Once I turned it off, it did work as before.

View >> Toad Options >> Editor >> Execute/Compile

This was the first thing checked and compare the 12.9 execute/compile options to the 12.8 and missed that one was new.

Very strange that a new option(which is basically a Toad Extension) that would invalidate existing sql would be made the default. #Really!$%!

Anyway, perhaps this post and your find will help others solve this problem quickly!

Thanks Again!

4

There have been enough complaints over the years by those choosing to work in scratchpad mode where the Editor text may not always be 100% syntactically correct code, SQL is broken apart to test a subquery, etc. The default to ON was chosen because it was thought that more people are negatively affected by the strict statement termination policy than those having blank lines in valid SQL. So far this is the second (or third) time the option has come up as a problem for valid SQL having blank lines. We’ll see how it plays out and if we need to make a change to the default at some point then we will do so.

Michael