Due to popular demand, we’ve added this new preference to the formatter. With it turned on:
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.
25 Comments
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.
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?
SIGH.
It’s not broken BAD, but it is broken. Or it looks off to me. Confirming with DEV to make sure.
same here. sad. hope to get this back soon.
it’s fixed for 18.3
Why?
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.
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.
Ohhhh… so pretty…..
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 –)
I’ll add a vote to this one. Blank lines is a vital part of readability (as well as what you write).
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
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”
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;
Would be better illustrated with ANSI syntax.
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.
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.
Can you have your code formatted, without having to format it?
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.
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
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.
Vadim, for the win 🙂