18.1 – New Formatting Option, Right-Align Query Keywords

thatjeffsmith SQL Developer 20 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 20

    1. thatjeffsmith Post
      Author
  1. 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?

    1. thatjeffsmith Post
      Author

      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.

  2. 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 –)

  3. 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

  4. 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”

  5. 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;

  6. 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?

    1. thatjeffsmith Post
      Author
      1. 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.

      2. thatjeffsmith Post
        Author
      3. thatjeffsmith Post
        Author

        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

      4. 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.

  7. 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

    1. 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.

      1. thatjeffsmith Post
        Author

Leave a Reply

Your email address will not be published. Required fields are marked *