Toad for Oracle 13.1 View Describe White spacing

Toad for Oracle & DevOps Toolkit
1

I've recently upgrade to Toad 13.1.0.78, and I've started noticing that when I bring up the describe window for some of my larger views, and look at the script on them, the white spacing has become excessive. For example, here's one small block in a larger view that I have:

WHERE ord . attribute6 IS NOT NULL
AND ord . header_id = line . header_id
AND line . flow_status_code <> 'CLOSED'
AND line . line_type_id = TYPE . transaction_type_id
AND TYPE . attribute1 = 'Y'
AND line . open_flag = 'Y'
AND TYPE . transaction_type_id = type1 . transaction_type_id
AND type1 . LANGUAGE = 'US'
AND line . ordered_quantity > 0
AND siteuse . site_use_id = ord . deliver_to_org_id
AND siteuse . cust_acct_site_id = acctsite . cust_acct_site_id
AND acctsite . party_site_id = partysite . party_site_id
AND acctsite . cust_account_id = hzacct . cust_account_id
AND hzacct . party_id = party . party_id
AND NVL ( ord . cancelled_flag , 'N' ) = 'N'
AND line . open_flag = 'Y'
AND ord . open_flag = 'Y'
--and nvl(msec.attribute1,'N') = 'N'
--and nvl(ord.attribute18,'No') = 'No'
AND line . subinventory = msec . secondary_inventory_name (+)
AND line . ship_from_org_id = msec . organization_id (+)
AND ord . invoice_to_org_id = h . site_use_id
AND h . gl_id_rev = gl . code_combination_id (+)
AND gl . segment3 = f . flex_value
AND f . flex_value_set_id = g . flex_value_set_id
AND g . flex_value_set_name

Here's the same block as viewed in Toad 11.6.

             WHERE     ord.attribute6 IS NOT NULL
                   AND ord.header_id = line.header_id
                   AND line.flow_status_code <> 'CLOSED'
                   AND line.line_type_id = TYPE.transaction_type_id
                   AND TYPE.attribute1 = 'Y'
                   AND line.open_flag = 'Y'
                   AND TYPE.transaction_type_id = type1.transaction_type_id
                   AND type1.LANGUAGE = 'US'
                   AND line.ordered_quantity > 0
                   AND siteuse.site_use_id = ord.deliver_to_org_id
                   AND siteuse.cust_acct_site_id = acctsite.cust_acct_site_id
                   AND acctsite.party_site_id = partysite.party_site_id
                   AND acctsite.cust_account_id = hzacct.cust_account_id
                   AND hzacct.party_id = party.party_id
                   AND NVL (ord.cancelled_flag, 'N') = 'N'
                   AND line.open_flag = 'Y'
                   AND ord.open_flag = 'Y'
                   --and    nvl(msec.attribute1,'N') = 'N'
                   --and    nvl(ord.attribute18,'No') = 'No'
                   AND line.subinventory = msec.secondary_inventory_name(+)
                   AND line.ship_from_org_id = msec.organization_id(+)
                   AND ord.invoice_to_org_id = h.site_use_id
                   AND h.gl_id_rev = gl.code_combination_id(+)
                   AND gl.segment3 = f.flex_value
                   AND f.flex_value_set_id = g.flex_value_set_id

Anyone have any idea what's going on here, and how I might be able to get back to normalized white spacing?

Edit: Looks like markdown is removing a lot of the excessive white space that exists. However, you can get an idea of it looking at the spacing that exists around the '.' separating the object aliases and column names. Now imagine instead of it being a single space, it's anywhere between 3 and 10, and you start to get an idea of what I'm seeing.

2

Sounds like formatter option changes to me.

You can turn off formatting of views by clicking the first toolbar button on the script tab.
In the dialog that appears, go to the “Views” tab, and uncheck “format”. WIth that turned off, the view’s query will come back with the same as it was when created (unless you created it with “select * from,”, in which case Oracle will expand out the fields)

If you prefer to change formatter options, that’s in main Toad options -> Formatter.

3

Turning off the formatter did get things back to looking reasonable.
I re-installed v13.0.0.80, and checked with that, and the formatting of my views looks reasonable there.

I compared all the Toad Options --> Formatter options between my 13.0 and 13.1 installs, and they are the same (only difference is an extra option for “Ensure the text ends with a new line” under Newlines in 13.1 that’s unchecked).

Seems that the formatting engine in 13.1 was updated, and it’s failing with some of our larger and more complex views.

4

Hi Evan, is it possible to paste here such “larger and more complex view”, I’ll be happy to compare 13.0.0.80 and 13.1.0.78. Or feel free to email it to me. (andre.vergison@…)
Please also include your formatter options. (options tree top tab, click on copy options)

Thanks,
Andre

5

Hi Andre,

I’ve emailed you the information that you requested. Looking forward to a response.

6

Hey Evan,

Andre and I are trying to reproduce your situation. For both of us (using your formatter settings), the formatted output looks OK. I am wondering if some other Toad options are coming into play.

Can you zip up and email me your User Files folder? It’ll be most likely under:
C:\Users(your user name)\AppData\Roaming\Quest Software\Toad for Oracle\13.1\User Files

Email to John.Dorlon@q…

Thanks.

7

Apparently the TYPE in the middle of the code seems to be at the root of the problem, the parser doesn’t seem to support it. We’ll fix that asap.

Thanks,
Andre