Formatter and parentheses (12.5.0.99)

Toad for Oracle & DevOps Toolkit
1

Format code works for this;

select * from user_tables ut where ut.table_name like ‘TEMP%’ or ut.table_name like ‘TMP%’ and ut.status = ‘VALID’

Formats to this:

select *
from user_tables ut
where ut.table_name like ‘TEMP%’
or ut.table_name like ‘TMP%’ and ut.status = ‘VALID’

But when I add parentheses like this:

select * from user_tables ut where (ut.table_name like ‘TEMP%’ or ut.table_name like ‘TMP%’) and ut.status = ‘VALID’

Format code does nothing:

select * from user_tables ut where (ut.table_name like ‘TEMP%’ or ut.table_name like ‘TMP%’) and ut.status = ‘VALID’

2

Did you set any formatter options? Can you paste your set here? (click on the Copy Options… button in the pane on the top of the tree and just paste the content).

Thanks,

Andre

3

Here are my formatter options:

252 5.259.0 5.265 1 1 1 140 2 2 2 2 0 3 2 0 1 W 6 99 2 0
4

Thanks. If you turn off the ParenthesizedQueryAsBlock option, that is “Format queries between parentheses as a block” in the DML Statements pane under Alignments, then it’ll format again.

I’m looking into this.

Thanks,

Andre

5

I made the option change as you suggested and now it formats like this:

select *
from user_tables ut
where (ut.table_name like ‘TEMP%’
or ut.table_name like ‘TMP%’)
and ut.status = ‘VALID’;

However, with the ParenthesizedQueryBlock on shouldn’t it format like this instead of doing nothing?

select *
from user_tables ut
where (
ut.table_name like ‘TEMP%’ or ut.table_name like ‘TMP%’
)
and ut.status = ‘VALID’;

6

You are right, but it’s a bug indeed. I fixed it and here’s the new output using your query where I copied the text to use as a subquery within parentheses:

Setting off:

  SELECT *
FROM (SELECT *
FROM user_tables ut
WHERE (ut.table_name LIKE 'TEMP%' OR ut.table_name LIKE 'TMP%'))
WHERE (ut.table_name LIKE 'TEMP%' OR ut.table_name LIKE 'TMP%')

Setting on:

  SELECT *
FROM (
SELECT *
FROM user_tables ut
WHERE (ut.table_name LIKE 'TEMP%' OR ut.table_name LIKE 'TMP%')
)
WHERE (ut.table_name LIKE 'TEMP%' OR ut.table_name LIKE 'TMP%')

Please note that this setting does not work on WHERE clauses between parentheses, it only works on entire queries.

Andre

7

Yes, I thought that setting only worked on entire queries which is why I didn’t try changing it to solve the problem initially. Thanks for the quick response Andre.

8

@Andre, you state above that you fixed it but I just installed 12.5.1 and it still has the problem. Did the fix not make it into 12.5.1?

Thanks