Due to popular demand, we’ve added this new preference to the formatter. With it turned on:

Some folks hate this, like Connor.

On

SELECT B.DEPARTMENT_NAME,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.SALARY
  FROM DEPARTMENTS B,
       EMPLOYEES A
 WHERE A.EMPLOYEE_ID = B.MANAGER_ID
   AND B.DEPARTMENT_ID (+) = A.DEPARTMENT_ID
 ORDER BY SALARY DESC,
          LAST_NAME ASC;

Off

SELECT B.DEPARTMENT_NAME,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.SALARY
FROM DEPARTMENTS B,
     EMPLOYEES A
WHERE A.EMPLOYEE_ID = B.MANAGER_ID
      AND B.DEPARTMENT_ID (+) = A.DEPARTMENT_ID
ORDER BY SALARY DESC,
         LAST_NAME ASC;

As always, send us your feedback! Example code always helps us make improvements.

Author

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

25 Comments

  1. Hi, how to exlcude line break on IN clause? Example (SQL Formatting)

    Before
    SELECT *
    FROM table
    WHERE field IN (
    ‘varchar2’, ‘varchar2’
    );

    After
    SELECT *
    FROM table
    WHERE field IN (‘varchar2’, ‘varchar2’);

    Just for you information, I have SQL Developer 19.1.0.094 Version.

    Thank you.

  2. Hi Jeff,

    I just upgraded my 18.1 version to 18.2 and the ‘Right Align Query Keywords’ no longer works for me. Not sure if this is conflicting with another setting for it to not work anymore?

    • Because someone asked for it. And by someone I mean, a lot of people. And that’s how we try to do things around here, esp with the formatter.

  3. Hey Jeff,

    I’ve been following the blog for a while and now I’m here to ask a question about the Bookmark functionality in SQL Developer. I can’t seem to get bookmarks to work in .sql files that are opened from the local computer, or in SQL Editor windows at all for that matter. When I Right-Click the line number gutter the option to Toggle Bookmarks appears, but doesn’t appear to do anything. Is this intended?

    • Bookmarks only work in the code editors (plsql), not in the SQL Worksheet. It’s technically a bug, but not easily fixable…the details would bore you, but believe me it’s something that annoys me as well.

  4. Suggestion for another setting: Reduce block of empty lines to Y line. (where Y can be defined)
    This would allow to shrink a block of empty lines to Y one(s) AND (very important): I often use an empty line for better reading (as paragraph distance in a document). Currently all empty lines are removed from the code beside those ones defined in the settings. This new setting would allow to keep my “private” empty lines (without the need to mark them as a comment –)

    • Vidar Eidissen

      I’ll add a vote to this one. Blank lines is a vital part of readability (as well as what you write).

  5. When having a function in a package with exactly 2 VARCHAR2 parameters VARCHAR2-Type is not alligned!
    FUNCTION chk_PW_valide (
    p_Passwort VARCHAR2,
    p_PasswortWdh VARCHAR2
    ) RETURN VARCHAR2 IS

    When having a third one or something like DEFAULT NULL alignment works correctly

  6. Please add “having count” clause to the alignment. Currently it looks like:
    FOR c IN ( SELECT tkl_code,
    tkl_name,
    tae_code,
    tae_haupt,
    COUNT(*) anz
    FROM v_org_tkl_tae
    GROUP BY tkl_code,
    tkl_name,
    tae_code,
    tae_haupt
    HAVING COUNT(*) > 1
    The having clause starts under “FOR”

  7. Ottmar Gobrecht Reply

    Hi Jeff, hi Vadim,

    Nice feature, but two problems:

    1. A case statement does not respect the “Right-Align Key Words” setting.
    2. The “AS” keyword after CASE END starts on a new line without indentation.

    Try it with the following settings and code:

    – Defaults after Installation
    – Alignment – Right-Align Query Keywords: checked
    – Line Breaks – SELECT/FROM/WHERE: unchecked

    SELECT
    CASE
    WHEN 1 = 2 THEN 42
    ELSE 0
    END
    AS test,
    NULL AS another_test,
    3 AS third_test
    FROM dual;

    SELECT NULL AS test,
    CASE
    WHEN 1 = 2 THEN 42
    ELSE 0
    END
    AS another_test,
    3 AS third_test
    FROM dual;

  8. Great to have right aligned key words. Nice addition. I did notice that Completion Insight: ‘Change case as you type’ has gone away. Is this an over sight or feature?

    • We removed that feature. Having 2 ways to change the text was causing so problems, and it seemed to better fit the Formatter.

    • Mark Casazza

      Any chance the formatter will be able to change case as I type in a future version? I really miss that feature. I don’t want (or need) to run every scrap of code through the formatter, but I would like what I’m typing to be formatted as I go.

    • Also, i’m looking at giving the formatter an option to ONLY touch the case of your keywords/identifiers in case you don’t like the rest of what it does

    • I would agree with this poster I miss having the Completion Insight: ‘Change case as you type’ . I would prefer if it came back as well.

  9. Jim Cruickshank Reply

    Hi Jeff,

    Nice feature but the example appears to show a separate bug in syntax colors. Why are the table alias identifiers A and B displayed in different colors on the screen shot? I get the same behavior using A as an identifier.

    Jim

    • Vadim Tropashko

      Jim, this is indeed a bug. Technically, ‘A’ is a keyword used in some obscure SQL syntax (e.g. expr ‘IS’ ‘A’ ‘SET’), and syntax highlighting never leveraged parsing. I checked all single character keywords, and non of them is compelling enough to highlight it — fixed for next release.

Reply To Dirk Cancel Reply