7 Replies Latest reply on Sep 17, 2017 9:00 PM by 3289113

    Auto Format Issue

    3289113

      Our architecture constructs queries we often need to validate through SQL Developer.

       

      The query text is returned as a single line, to which I would typically use the Auto format feature to set out the query in a readable structure, and then attempt to run it to determine and correct the faulty line(s).

       

      Unfortunately, after updating to version 4.2.0, (I understand that 17.2 has the same issue) the text will not be formatted and will instead split the text into two lines, showing any expected syntax errors etc.

       

      This is a frustrating feature, as the text needs to be corrected before it will allow me to format it.

       

      Is it possible to force SQL Developer to format the text regardless of syntax errors etc? (this was possible in earlier versions - in fact, it was default functionality)

       

      Thanks for any help, and I'm sorry if anyone has asked  this already, I have searched but have found nothing specific enough to help me.

        • 1. Re: Auto Format Issue
          Gaz in Oz

          It'd be nice if you copy/pasted an example sql query and the SPECIFIC steps you take so others may be able to try and reproduce what you are doing.

          ...and by "previous version" do you mean 4.1.5..21.78?...

          Be specific when mentioning versions of things.

          • 2. Re: Auto Format Issue
            3289113

            Example:

                   Original Query:    select cust_account_id, account_number from hz_cust_accounts c,hz_parties p where  c.cust_account_id = 10 and c.party_id  p.party_id

                

                 After Format Attempt:

             

                      select cust_account_id, account_number from hz_cust_accounts c,hz_parties p where  c.cust_account_id = 10 and c.party_id

                      /*** Syntax Error at line 0, column 120

             

                      select cust_account_id, account_number from hz_cust_accounts c,hz_parties p where  c.cust_account_id = 10 and c.party_id  p.party_id

                                                                                                                                    ^^^        

                      Expected:

                       ***/  p.party_id

             

            Specific Steps I would take:

                 Copy Query to clipboard

                 Paste query into new SQL Developer worksheet

                 CTRL F7

             

            Previous Version that does not feature this syntax error functionality:

                 4.1.5.21 Build MAIN-21.78

             

            Format above version presents data after auto format:

                 SELECT cust_account_id,

                   account_number

                 FROM hz_cust_accounts c,

                   hz_parties p

                 WHERE  c.cust_account_id = 10

                 AND c.party_id  p.party_id

                

            Apologies if this was unclear.

            • 3. Re: Auto Format Issue
              thatJeffSmith-Oracle

              and then attempt to run it to determine and correct the faulty line(s).

               

              The parser shows that immediately - look for the grammar squiggle.

              bad1.png

              Adds '=' for valid predicate

               

              SHIFT+F7   

              bad2.png

              • 4. Re: Auto Format Issue
                3289113

                Thanks for the response, but that's not the issue i'm trying to solve.

                 

                My example was a very simple query that doesn't take long to see the error within - I only provided it to explain the auto formatting structure issue.

                 

                More often, our queries are potentially hundreds of lines (after being formatted) but are returned by default on a single row.

                 

                I want to re structure the single line into the hundreds of lines for legibility, regardless of syntax.

                 

                Here's a more relevant example.

                 

                Original SQL provided:

                 

                select bank.masked_bank_account_num bank_account, party.party_name branch , uses.instrument_id from iby_external_payers_all pay, iby_pmt_instr_uses_all uses , iby_ext_bank_accounts bank , hz_parties party where bank.branch_id = party.party_id AND uses.ext_pmt_party_id = pay.ext_payer_id  AND uses.payment_flow = 'FUNDS_CAPTURE' and bank.ext_bank_account_id = uses.instrument_id AND pay.cust_account_id = [INVOICE|CUSTOMER NUMBER:customer_id] AND (  pay.acct_site_use_id = [INVOICE|BILL_LOCATION:Site_use_id] OR pay.acct_site_use_id IS NULL )

                 

                Auto Format on my PC: Version 4.2.0.16.356 Build 16.356.1154

                 

                select bank.masked_bank_account_num bank_account, party.party_name branch , uses.instrument_id from iby_external_payers_all pay, iby_pmt_instr_uses_all uses , iby_ext_bank_accounts bank , hz_parties party where bank.branch_id = party.party_id AND uses.ext_pmt_party_id = pay.ext_payer_id  AND uses.payment_flow = 'FUNDS_CAPTURE' and bank.ext_bank_account_id = uses.instrument_id AND pay.cust_account_id =

                /*** Syntax Error at line 0, column 404

                 

                 

                select bank.masked_bank_account_num bank_account, party.party_name branch , uses.instrument_id from iby_external_payers_all pay, iby_pmt_instr_uses_all uses , iby_ext_bank_accounts bank , hz_parties party where bank.branch_id = party.party_id AND uses.ext_pmt_party_id = pay.ext_payer_id  AND uses.payment_flow = 'FUNDS_CAPTURE' and bank.ext_bank_account_id = uses.instrument_id AND pay.cust_account_id = [INVOICE|CUSTOMER NUMBER:customer_id] AND (  pay.acct_site_use_id = [INVOICE|BILL_LOCATION:Site_use_id] OR pay.acct_site_use_id IS NULL )

                                                                                                                                                                                                                                                                                                                                                                                                                                    ^^^                                                                                                                                      

                 

                 

                Expected: expr#,simple_expression,name_wo_function_call,iden

                ***/ [INVOICE|CUSTOMER NUMBER:customer_id] AND (  pay.acct_site_use_id = [INVOICE|BILL_LOCATION:Site_use_id] OR pay.acct_site_use_id IS NULL )

                 

                Auto Format on Coworkers PC: Version 4.0.2.15 Build 15.21

                 

                SELECT bank.masked_bank_account_num bank_account,

                  party.party_name branch ,

                  uses.instrument_id

                FROM iby_external_payers_all pay,

                  iby_pmt_instr_uses_all uses ,

                  iby_ext_bank_accounts bank ,

                  hz_parties party

                WHERE bank.branch_id         = party.party_id

                AND uses.ext_pmt_party_id    = pay.ext_payer_id

                AND uses.payment_flow        = 'FUNDS_CAPTURE'

                AND bank.ext_bank_account_id = uses.instrument_id

                AND pay.cust_account_id      =[INVOICE|CUSTOMER NUMBER:customer_id]

                AND ( pay.acct_site_use_id   = [INVOICE|BILL_LOCATION:Site_use_id]

                OR pay.acct_site_use_id     IS NULL )

                 

                I hope this makes my issue clearer.

                Cheers

                • 5. Re: Auto Format Issue
                  thatJeffSmith-Oracle

                  In 17.2, when you format the query, the cursor auto-advances to where the parser gets confused, so you don't have to hunt and peck where the issue is

                   

                  format.png

                   

                  >>regardless of syntax.

                  The new formatter is parser driven. The previous formatter was based on some very simple REGEX patterns. The new formatter can't format something it doesn't understand. You may want to keep a copy of 4.1 on hand to help you with your generated SQL.

                   

                   

                  • 6. Re: Auto Format Issue
                    Vadim Tropashko-Oracle

                    With the last query

                     

                    SELECT bank.masked_bank_account_num bank_account ...

                     

                    cloned 3 times so that it occupies enough space to render a scroll bar, the cursor fails to point to the error.

                     

                    You have to scroll up and put the cursor at the top of the editor (actually, anywhere before the error), try format, then cursor jumps to the error.

                     

                    I filed a bug.

                     

                     

                    P.S. formatting syntactically invalid code to get a hint what is wrong with it is a challenging enhancement request.

                    • 7. Re: Auto Format Issue
                      3289113

                      The new formatter is parser driven. The previous formatter was based on some very simple REGEX patterns

                      Aha! That explains the difference. Thank you. Unfortunately that doesn't solve my problem, but i'm grateful for an explanation.

                       

                      P.S. formatting syntactically invalid code to get a hint what is wrong with it is a challenging enhancement request.

                      Don't get me wrong, I understand the challenge, I was hoping that it would be simple given it was available in earlier versions.

                       

                      I was wondering if it's possible to add the REGEX formatting to the parsing formatter.

                      I.e. perform the Regex format, then format through parser. (optionally of course) This would give me the best of all worlds, but i'm not sure how possible this is..