Beautifier runs forever...

[jto]

Hello,
this query below "kills" beautifier.

select country_code, From_Date, To_Date, Info
  from (select country_code,
               sales_date as From_Date,
               case
                 when Next_Diff = 1 then
                  Lead(sales_date) Over(partition by country_code order by sales_date)
                 else
                  sales_date
               end as To_Date,
               case
                 when Prev_Diff = 1 then -- has already been counted in previous interval
                  'ignore'
                 else
                  'use' -- probably is a single day
               end as Info
          from ( -- just get ordered info about dates per market
                select country_code,
                        sales_date - Lag(sales_date, 1) Over(partition by country_code order by sales_date) Prev_Diff,
                        sales_date,
                        Lead(sales_date, 1) Over(partition by country_code order by sales_date) - sales_date Next_Diff
                  from (select distinct country_code, sales_date -- get the different dates
          from (select country_code,  sales_date, tot_fifo,qty_fifo_Sum
                  from (select *
                          from ((select country_code,  sales_date, tot_fifo,qty_fifo_Sum
                                   from sys_fifo_control_sums
                                  where System_Id = 'ORI') minus
                                (select country_code,  sales_date, tot_fifo,qty_fifo_Sum
                                   from sys_fifo_control_sums
                                  where System_Id = 'DWH'))

                        union all
                        select *
                          from ((select country_code,  sales_date, tot_fifo,qty_fifo_Sum
                                   from sys_fifo_control_sums
                                  where System_Id = 'DWH') minus
                                (select country_code,  sales_date, tot_fifo,qty_fifo_Sum
                                   from sys_fifo_control_sums
                                  where System_Id = 'ORI'))))
         )
)
         where not (Nvl(Next_Diff, 2) = 1 and Nvl(Prev_Diff, 2) = 1))

PL/SQL Developer
Version 7.1.1.1339
39044 - 10 user license
Windows XP Professional 5.1 Build 2600 (Service Pack 2)

Physical memory : 2,096,360 kB (1,148,896 available)
Paging file : 3,991,280 kB (3,112,688 available)
Virtual memory : 2,097,024 kB (1,995,980 available)

Parameters
C:\Program Files\PLSQL Developer 7.1\PlSqlDev.exe
/nosplash

Preferences
Session mode: Dual
OCI Library:
Use OCI7: False

Preference Files
C:\Program Files\PLSQL Developer 7.1\Preferences\Default\Default.ini
C:\Program Files\PLSQL Developer 7.1\Preferences\JiriO\default.ini

Plug-Ins

Aliases
BXLDW
CDW
CDWH
EM
EXTPROC_CONNECTION_DATA
...

Homes
BITools (C:\oracle\BITools)
OraDb10g2 (C:\oracle\10g2)
OraDesig (D:\Oracle\Designer)

DLLs
C:\oracle\10g2\bin\oci.dll

TNS File
c:\oracle\tnsnames.ora

Using
Home: OraDb10g2
DLL: C:\oracle\10g2\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Character Sets
Character size: 4 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.AL32UTF8
NLS_CHARACTERSET: AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16

best regards
Jiri

 

[Marco Kalter]

It formats instantaneously for me. Can you send me your beautifier rules (.br) file?

 

[jto]

Version=1
RightMargin=80
Indent=2
UseTabCharacter=FALSE
TabCharacterSize=2
AlignDeclarationGroups=TRUE
AlignAssignmentGroups=TRUE
KeywordCase=2
IdentifierCase=3
UseSpecialCase=TRUE
ItemList.Format=1
ItemList.Align=TRUE
ItemList.CommaAfter=TRUE
ItemList.AtLeftMargin=FALSE
EmptyLines=1
ThenOnNewLine=FALSE
LoopOnNewLine=FALSE
DML.LeftAlignKeywords=FALSE
DML.LeftAlignItems=FALSE
DML.OnOneLineIfPossible=TRUE
DML.WhereSplitAndOr=TRUE
DML.WhereAndOrAfterExpression=FALSE
DML.WhereAndOrUnderWhere=TRUE
DML.InsertItemList.Format=1
DML.InsertItemList.Align=FALSE
DML.InsertItemList.CommaAfter=TRUE
DML.InsertItemList.AtLeftMargin=FALSE
DML.SelectItemList.Format=1
DML.SelectItemList.Align=TRUE
DML.SelectItemList.CommaAfter=TRUE
DML.SelectItemList.AtLeftMargin=FALSE
DML.UpdateItemList.Format=2
DML.UpdateItemList.Align=TRUE
DML.UpdateItemList.CommaAfter=TRUE
DML.UpdateItemList.AtLeftMargin=FALSE
ParameterDeclarationList.Format=1
ParameterDeclarationList.Align=TRUE
ParameterDeclarationList.CommaAfter=TRUE
ParameterDeclarationList.AtLeftMargin=FALSE
RecordFieldList.Format=1
RecordFieldList.Align=TRUE
RecordFieldList.CommaAfter=TRUE
RecordFieldList.AtLeftMargin=FALSE
SplitAndOr=FALSE
AndOrAfterExpression=FALSE
[SpecialCase]

 

[Dezsoe Kados]

Maybe removing bookmarks and/or breakpoints could help? (I think there is some thread mentioning this)

 

[jlcox]

Trying to beautify this locks up my PC also, even with default beautifier settings. I searched this forum because I have a nested query that, if beautified, puts my PC into hardcore 100% CPU lockup. After waiting for 10 minutes for the beautifier to finish, I finally killed the process. I can send the query along if you want to try it.

 

[Marco Kalter]

Yes, please send me the SQL text and your rules file (if any).

 

[isolbar]

I also have a problem with the Beaytifier. It fails on large SQL and PL/SQL but works on small blocks of code.