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.
Exchange request to treat line breaks on commas consistently.
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.’
And
Just handle new stuff better – like in-line PL/SQL with your SQL statements.
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%.
45 Comments
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.
are you on 18.1?
here’s a query with cols in the select aligned
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
give me a code sample, formatted exactly as you’re expecting.
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
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 …
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!
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)I can’t reply to all these comments – please post separate, complete test cases on the SQL Developer OTN forum.
there is, in latest build it looks exactly as your code sample shows
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'; ELSEinstead it should be
IF in1 = '1' THEN out1 := '1'; out2 := '2'; ELSEIn 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’;
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;that’s how i see it in latest build, THEN is on same line as IF
Please add an property that a break should occur before WHERE keyword of sql statements:
DELETE FROM emp WHERE id in ...Please add an property that a break should occur before RETURN keyword of function definitions:
FUNCTION dummy(in IN VARCHAR2) RETURN BOOLEANPlease add a configuration option, that sql keywords of a select should be right aligned:
SELECT sysdate FROM dual;+1
Please add a property that selects, calls, paramterlists etc. should only formatted with breaks, when the “max char line width” is exceeded.
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);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?
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?
I need a code sample/example – that’s indeed EXACTLY what it’s supposed to do.
Sorry, missed your reply. This happens with any package
I’m not seeing that. How about a screenshot? Start a thread on the forums.
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' ENDOur 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.cond2Overall 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.
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: ***/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
)
If you enable Line Breaks – After SELECT/FROM/WHERE, you’ll get just that.
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
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! 😉
‘select your data source’ – you mean the drop-down on the worksheet to change connections?
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.
Not today
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
Yo could do something like http://poorsql.com/
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.
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
)
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
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
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.