The old formatter, the code that’s used to format your code from v1 to version 4.1 was based on some pattern matching code and expressions. It was very limited, and didn’t like a lot of syntax and coding styles common in Oracle code.

It also wasn’t very flexible.

So we built a new engine. One that takes advantage of our Oracle parser.

The new formatter (the part you can see like the preferences) looks very much like the old formatter. So you don’t get a new paint job or leather seats, but the engine under the hood is crazy good and the transmission and power train is much better at getting that horsepower to the wheels. Sorry for the car metaphor, but it kinda works here I think.

For the EA, it would be most helpful if you could try the formatter in version 4.2. We’re looking for two things.

1. The parser chokes and the formatter is uanble to format your code.
That will look like this:

WITH t(ID,text) AS (  
  SELECT 1,'one,two,three' FROM dual  
  UNION ALL  
  SELECT 2,'un,deux,trois' FROM dual  
  UNION ALL  
  SELECT 3,'een,twee,drie' FROM dual  
)  
SELECT id,  
  TRIM(COLUMN_VALUE) text  
FROM t,  
  xmltable(
/*** Syntax Error at line 10, column 12
 
 
  xmltable(('"'  
            ^^^    
 
Expected: string_literal,
 ***/('"'  
    || REPLACE(text, ',', '","')  
    || '"'))  
;

Please post your code samples to the Forums so we can fix those for the next EA, and of course for the official v4.2 release.

2. The formatter preferences aren’t flexible enough to get the code out the way you want it to look.
Good formatting is a purely subjective point of view. What I like fine you might hate. But we can all agree that the formatter should be flexible enough to not spit out code that no one can use.

Please let us know what additional preferences you would like.

I can say already that we omitted a few Alignment preferences that we shouldn’t have, and we’re going to add them for the next drop.

We’re NOT going to end with 3,000 formatter options by the way. But I can see us definitely adding a few.

The good news is that the new formatting engine is MUCH easier to enhance, and it will get better with each release now as long as folks continue sending in their ideas and requests.

Concrete Examples of Improvements

Exchange request to align a SELECT list.

Before and After - desired effect being the 'after.'
Before and After – desired effect being the ‘after.’

Exchange request to treat line breaks on commas consistently.

They wanted BEFORE to apply to SELECT lists AND proc arguments.
They wanted BEFORE to apply to SELECT lists AND proc arguments.

Exchange request to give more flexibility on text CASE preferences. Upper, lower, InitCap, leave alone. We did that, and have it for both keywords like ‘SELECT’ and ‘LOOP’ and identifiers like ‘HR’ and ‘EMPLOYEES.’

Case options before.
Case options before.

And

Case options after.
Case options after.

Just handle new stuff better – like in-line PL/SQL with your SQL statements.

It just looks better and is easier to read.
It just looks better and is easier to read.

Sharing Your Examples

Always please include your sample code along with your parser errors and formatting examples. It will increase your odds of a favorable outcome by about 1,000%.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

45 Comments

  1. Is there a way to align columns (previous function “Align on Keyword As”)? In the lower versions of SQL Developer you could align all columns that used “As” for a column.

    • thatjeffsmith

      are you on 18.1?

      here’s a query with cols in the select aligned

      SELECT BEER_NAME 
            ,BREWERY_NAME 
            ,BEER_TYPE 
            ,BEER_ABV 
            ,BEER_IBU
            ,CHECKIN_COMMENTS
            ,VENUE_NAME
            ,VENUE_CITY
            ,VENUE_STATE
            ,VENUE_COUNTRY
            ,VENUE_LAT
            ,VENUE_LNG
            ,RATING_SCORE
            ,CREATED_AT
            ,CHECKIN_URL
            ,BEER_URL
            ,BREWERY_URL
            ,BREWERY_COUNTRY
            ,BREWERY_CITY
            ,BREWERY_STATE
            ,FLAVOR_PROFILES
            ,PURCHASE_VENUE
            ,SERVING_TYPE
        FROM UNTAPPD
       WHERE 1 = 1
       fetch FIRST 25 ROWS ONLY;
    • Yes, 18.1.

      I would like to align each column that has an “As” ..

      I suspect it has to be written in the new advanced formatter.

      Thanks for the reply, all help apprciated!
      Kevin

    • Hope this come across OK… needs to be fixed width font.

      Select
      ‘Customer’ As Column1
      , ‘Order’ As Column2
      , ‘Amount’ As Column3
      , ‘ReallyLongColumnName’ As Column4
      From
      Dual

      Copy and paste into Text editor as Courier New font if the “As” are not aligned.
      Thanks,
      Kevin

    • thatjeffsmith

      i don’t see alignment, how about you just share a screenshot of how you want it to look.

    • It didn’t come across aligned maybe this will show what I’m trying to do.

      SELECT
      ‘CUSTOMER’ AS COLUMN1
      , ‘ORDER’ AS COLUMN2
      , ‘AMOUNT’ AS COLUMN3
      , ‘REALLYLONGCOLUMNNAME’ AS COLUMN4
      FROM
      DUAL

    • Sorry, neither worked.

      I’m trying to replicate the older function in 4.1.5

      Alignment…

      “Align on ‘AS’ Keyword …

    • thatjeffsmith

      Yeah, that’s one of the options we nuked, didn’t see many folks using it. Looks like we missed you. I can put it on a list to redo.

      The old formatter code wasn’t ours, and it wasn’t very good. So we wrote one from scratch, so it obviously has differences. I have a copy of 4.1.5 hanging around so I can go have a go with yours.

    • Thanks for the interest in possibly adding it back.

      v18.1’s formatter is pretty close to what I’ve been working with. Having a good formatter is a must, :-).

      Thanks again and awesome work to date!

  2. There should be an option to align the type an IN/OUT definition of parameters:

      FUNCTION fk_exist_pebkzuo(
        in1  IN  NUMBER
      , in2  IN  VARCHAR2
      , out1 OUT DATE)
    
    • thatjeffsmith

      I can’t reply to all these comments – please post separate, complete test cases on the SQL Developer OTN forum.

  3. In 4.2 EA2 I have seen that more than more than one statement happens in the then block then on statement is one the same line, next is on the next line, this should not happen, as this not readable:

          IF in1 = '1' THEN out1 := '1';
            out2 := '2';
          ELSE
    

    instead it should be

          IF in1 = '1' THEN 
            out1 := '1';
            out2 := '2';
          ELSE
    
  4. In 4.2 EA2 I have seen that more than if statement happens on one line, this should not be happen, as this not readable:

    ELSIF in1 = ‘A’ THEN IF in2 = ‘B’ THEN out := ‘C’;

    
    
  5. Please add an property that allows then to be on the line where if/elsif is defined:

        IF in1 IS NOT NULL THEN
          out := 1;
        ELSIF in2 = 'A' THEN
          out := 2;
    
  6. Please add an property that a break should occur before RETURN keyword of function definitions:

      FUNCTION dummy(in IN VARCHAR2)
        RETURN BOOLEAN
    
  7. Please add a property that selects, calls, paramterlists etc. should only formatted with breaks, when the “max char line width” is exceeded.

  8. Please add a property to allow leading comma’s in parameterlists, these are easier to delete and to add:

      TYPE tRec IS RECORD(
        par1 VARCHAR2(2)
      , par2 NUMBER(1)
      , par3 BOOLEAN DEFAULT FALSE);
    
  9. Good morning, in version 4.1 there was a parameter that can not find in this new version 4.2. The parameter is: “Rigth-Align Master Keywords”. Do you have removed this option?

    • I’m missing the Right-Align Master Keywords option as well. I hope that will be added back.

      I love that the Select list is now aligned, in 4.2, but does adding that feature mean we have to give up the right-align option of the keywords?

  10. Hi,
    I tried to use the code outline and I was hoping that when I click on a member procedure or function in the outline it will set the cursor to the first line of that procedure, instead it sets the cursor somewhere else, to some random position. Is this a bug or feature?

  11. I would appreciate more flexibility with ANSII Joins and CASE statements.

    For CASE we’d like to be able to have WHEN, THEN and ELSE indented once from CASE and END so that the case forms it’s own block.

    CASE "1"
        WHEN 1
        THEN 'XX'
    END
    

    Our JOIN usage might be a little more uncommon but we’d like to be able to have the word JOIN indented and have ON remain on the same line as the JOIN

    FROM
        tablea a
        JOIN tableb b ON
            a.cond1 = b.cond1 AND
            a.cond2 = b.cond2
    

    Overall so far I’m able to get closer to what we like with the new options. It would also be handy to be able to apply a different style to small sub queries.

    • Justin H

      Also if you have SQLCL commands in the buffer and accidentally format the whole thing it will throw errors into comments.

      /*** Syntax Error at line 642, column 5
      
      
      info
           ^^^
      
      Expected: 
       ***/
      
  12. Alan Patil Reply

    Hi Jeff, I might have missed the option but I would like to have the option to format the sql so the brackets line up horizontally and everything inside the brackets is indented one tab. For example

    select a, b
    from
    (
    select x a, y b
    from x,
    (
    select y
    from z
    where 1=1
    )
    where y=1
    )

  13. Kevan Gelling Reply

    Here’s my two (or four) cents:

    * Align columns (and tables) on name rather than comma, if commas precede.
    * Align = in conditions and => in calls
    * Indent parameters under procedure name in calls
    * 1 line per condition with AND and the condition on the same line

    Thanks

  14. When I go to SQL in the toolbar to select my data source, the worksheet is slightly slower to open in 4.2 vs 4.1.3 (jdk 7u51). Maybe it is due to newer Java version or the application itself? I think it is due to the newer Java version. I ran 4.2.0 and jdk 7u51 and it runs faster for me and SQL toolbar is more responsive. Makes me want to run jdk 6 and see what happens! 😉

    • thatjeffsmith

      ‘select your data source’ – you mean the drop-down on the worksheet to change connections?

  15. Scott Johnston Reply

    Any plans in the new version to allow changing the background color of the code editor based on the connection? We assign a color to each instance in the connections window to help prevent mixing up dev, testing and production. However, once you have the code editor windows up it’s easy to mix them up. If the code editor window matched the connection color, it would be more apparent which you were working in.

  16. Angelo Bernardi Reply

    Hi!
    I’ve been waiting Formatter improvements, but, for now, I’ll keep with 4.1.
    I’ve lost many options that I usually use.
    For example, ANSI JOINS:
    What I have configured on 4.1:
    SELECT *
    FROM table_1 t1
    INNER JOIN table_2 t2
    ON t1.key = t2.key
    AND t2.condition = TRUE;

    Now, on 4.2 I only have 2 options:
    ‘Single line ANSI Joins’ and this:
    SELECT *
    FROM table_1 t1
    INNER JOIN
    table_2 t2
    ON
    t1.key = t2.key AND t2.condition = TRUE;

    Among another things…

    Also, if I use UNION sentence, the new formatter doesn’t put it on a new line, like:

    SELECT a,b
    FROM t1
    WHERE a=b UNION SELECT a
    ,b
    FROM t2
    WHERE ab;

    instead of

    SELECT a,b
    FROM t1
    WHERE a=b
    UNION
    SELECT a,b
    FROM t2
    WHERE ab;

    That’s what I’ve found on my 20minute tryout.

    Rgds

    • thatjeffsmith

      OK, the UNION will get written to a sep line for the next update.

      Please share your ANSI SQL Join syntax preferences to the Forums so we can collect feedback from other users.

  17. Please view the sample with a non proportional font! Then in first sample the comments are aligned.
    Additional question when using line breaks before comma:
    Allow 1. parameter without leading comma to start on same column as subsequent parameters.
    FUNCTION a (
    p_1
    , P_2
    , p_3
    )
    currently it’s
    FUNCTION a (
    p_1
    , P_2
    , p_3
    )

  18. Hi Jeff,
    concerning formatter having a comment after parameter definition:
    What I want (From formatter sample code):
    — create_package_body.htm#LNPLS01381
    CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
    tot_emps NUMBER;
    tot_depts NUMBER;

    FUNCTION hire (
    last_name VARCHAR2, — Comment
    job_id VARCHAR2, — Comment
    manager_id NUMBER, — Comment
    salary NUMBER,
    department_id NUMBER
    ) RETURN NUMBER IS
    new_empno NUMBER;
    BEGIN

    What I get:
    CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
    tot_emps NUMBER;
    tot_depts NUMBER;

    FUNCTION hire (
    last_name VARCHAR2,– Comment

    job_id VARCHAR2,– Comment

    manager_id NUMBER, — Comment

    salary NUMBER,
    department_id NUMBER

    => Please remove the additional line break after a comment behind a parameter
    => additional alignment option for comments behind statements

    Is there any “formatter hint” option to tell the formatter NOT to do any changes e.g. between the 2 lines
    /#-#/
    — No formatting done
    /#+#/
    Similar to the optimizer hints?
    Regards,
    Dirk

    • Jim Cruickshank

      I have the same issue as Dirk with extra line breaks after comments. I’d prefer it never added line breaks after single line comments.

      Thanks,

      Jim

    • thatjeffsmith

      I think we fixed the double line break on the comments – I can’t make that happen in our latest build.

      There’s no ‘ignore’ code here switches. The best you can do is control by highlighting only what you want formatted.

Write A Comment