This post is a tease. It’s also a request.
Send me your ugly code.
In version v4.2 of SQL Developer – to be released in calendar year 2016 – we’ll be offering a new formatter for your SQL and PL/SQL. It’s got a completely new engine under the hood. It’s faster. It’s more efficient. And it sounds really cool too – which is what most gearheads care about most.
I’ve taken many, many, many requests for a ‘better’ formatter as product manager for SQL Developer. Now it’s time for some explicit requests. Like, I need your ugly code, and I need to know how you want to make it look, or where our current formatter ‘breaks’ your code.
As an example, we have this request on the Exchange:
--SQL Formatter API --Description: I can't even cover all the issues I have with the SQL formatter, so I'm just going to request a formatting API. --Here is just 1 scenario where the SQL formatter does not meet our needs: --Desired: CREATE OR REPLACE PACKAGE BODY "PKGX" AS FUNCTION FX(P1 IN INTEGER, P2 IN INTEGER) RETURN INTEGER IS VX INTEGER := 0; Vy INTEGER := 1; BEGIN FOR i = 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; RETURN 0; END FX; END "PKGX";
Here’s what it formats to in version 4.2 with the default settings:
Yes, we will have an Early Adopter/Beta process for version 4.2. So you’ll be able to have a go at the new formatter before it’s final. Send me your code and your comments, and I’ll give it a whirl and see how much closer we are to your code nirvana in 4.2.
[email protected]
There are 18 requests on the Exchange for the formatter. I’m reviewing all of them now. Make sure you get yours into the system while the oven is hot and ready for them 🙂
53 Comments
Thanks for the information! I want to add one more tool to this list – I use SQL Formatter to make my code better. This is an online service Online SQL Formatter
Interesting, but it’s for ‘SQL Server’, not Oracle…which is what most folks here care about most.
I’m glad you found a way to get your code the way you want. I do request though that if you are using SQL Developer, and you find a feature lacking, let us know so we can address it or even show you how to get what you want.
I realize that this thread is about an older version of SQL Developer, but I couldn’t find a similar thread for v17. My team recently moved from Toad to SQL Developer because we only use Oracle and it seemed to be a robust tool that met our needs. The formatter is specifically important because we use it to enforce coding standards. Unfortunately, it didn’t take long to find an issue.
For example, I would like this unformatted SQL…
with escalation_order AS
(
select unit_id
from order__table
where name = ‘exception_ind’
and upper(value) = ‘YES’
)
select eo.unit_id
from escalation_order eo
join on item_table it on eo.unit_id = it.action_id AND it.type = ‘XX’;
…to look something like this after formatting.
WITH escalation_order AS
(
SELECT
unit_id
FROM
order_table
WHERE
name = ‘EXCEPTION_IND’
AND UPPER( value ) = ‘YES’
) SELECT
eo.unit_id
FROM
escalation_order eo
JOIN ON item_table it
ON eo.unit_id = it.action_id
AND it.type = ‘XX’;
Unfortunately, the formatter wouldn’t make any changes to the query. I tried selecting parts of the query and applying the formatter and it worked until I reached the “JOIN ON” part. It seems that the formatter does not like ANSI join syntax. This is disappointing since Oracle obviously supports it and we use it frequently.
Additionally, I would like standard Oracle function names (ex. UPPER) to be considered as keywords for the purpose of capitalization.
And finally, the formatter should report error messages when it encounters code that prevents it from formatting. It would have made it easier to understand that it didn’t recognize something rather than doing nothing and wondering why.
Take a simple mistake like this:
select from dual;
The formatter does nothing and gives you know clue why not. Of course in a simple query like this, it is easy to see that I forgot to specify the columns to select, but why not report the error?
Of course all the indentation was lost in the web form, but I think the idea is still clear.
are you on 17.4? b/c it will format the code up until the point it gets ‘confused’…btw we do support ANSI SQL, although i see an issue with your example, obviously.
but if I ask it format this…:
…it handles it just fine.
We do report issues, but instead of marking up your pretty code with comments, which we were previously doing, we mark it up with error indicators. These are more obviously seen when yo have the code outline open.
Your example:
I’ll log a bug for your example, that should be working.
Displaying the errors the way 17.4 is doing it is fine. I just wanted to see it somehow. Unfortunately I am using 17.2. It requires navigating a slow bureaucracy to get software upgraded at my company, so it will probably be months, or longer before I see 17.4.
And actually I realized, after I posted, that the mistake is mine. There is an extra “ON” after the first “JOIN”. But I became confused because I wasn’t seeing the error. Once I removed the ON, then the formatter was able to handle it.
Ah, well I hadn’t gotten to proofing your SQL yet, but thanks for the update 🙂
If you or your team need any help getting up to speed with SQLDev, please feel free to email me at [email protected]. I’m always happy to do free webinars for folks.
Jeff,
Is there a nice way to right align keywords? I seem to remember this being in there before, but now in 17.3 it appears that it’s not there any more…
There is something so nice about right aligned keywords that makes very long SQL statements so much easier to read.
Rich
I have never understood the urge to right-align code blocks, and it has been been irritating the hell out of me since it first appeared in the mid 90s. It seems to me that the entire point of indenting code, in any style and in any language, is to create visual blocks indicating dependency levels, and right-aligning – which nobody would dream of doing in any other language – entirely defeats that by creating an amorphous blob that is no help at all in taking in the structure. I feel like signing a petition to ban it.
William,
For you, sure.
However, for many folks, SQL is a fundamentally different language than something like Java or Python or C, etc.
SQL Queries have very distinct clauses and it can be very nice to see each of those clauses lined up as a ‘gutter’, particularly when you use ANSI query syntax (I feel like signing a petition to ban the Oracle proprietary syntax where you can’t even do a full outer join and where clauses both join tables and filter rows).
Think of it like the outline view of the query; “Here is the select clause, here is the join clause(s), here are the where clause(s), here is the order by clause, etc.
I totally understand you are not a fan, but do you really think that because you don’t like it, that no one else should be allowed to use it?
You say “It seems to me that the entire point of indenting code, in any style and in any language, is to create visual blocks indicating dependency levels, and right-aligning – which nobody would dream of doing in any other language – entirely defeats that by creating an amorphous blob that is no help at all in taking in the structure.”
I’d say “The goal of ‘extraneous non-printing characters’ in code is to improve readability of the code and only one set of ‘extraneous characters’ is indents, while others are things like aligning arguments, concatenation, datatypes, etc.
And to be clear, I’m not right aligning the code blocks, but instead right aligning the keywords used for each of the ‘code blocks’ in a SQL query.
But that’s me. You are different… And, if the formatter in SQL Developer worked just a bit better, we could both have what we want.
I wonder whether Java IDE vendors get requests to right-align keywords.
I don’t know if i’m late or i miss something, but can i make formatter on “AND” clause like on 4.1? Here’s comparison:
4.2 :
AND
1 = 1
AND
2 = 2
4.1 :
AND 1 = 1
AND 2 = 2
Thanks~
I think I landed here too late. 4.2 is already in early adopter…
My team is using PLSQL Developer from AllroundAutomations. The one and only reason we are still using it is the code beautifier. It does have a great set of features. BUT, some of them are buggy and the author just ignores all of the fix requests.
We really want to switch to Oracle’s SQL Developer, and especially for the option to use Git with it.
Is there a plan for you to implement the features that they have (one that you lack is the “on one line if possible” which makes formatting really intelligent. But that is just one.
We have more than 600 000 lines of code and at least a 3 years of history in git. In order to migrate the tool we have to make sure the new tool formats with the same rules.
I can’t promise, nor would I want to, to just copy another tool’s formatting rules.
If you want to give me a few code samples, I’ll see what I can do though.
Tell me more about this ‘one line if possible’ rule.
I just wrote a huge answer, but I don’t see it. Is it pending, maybe?
I just checked the SPAM box, nothing there.
Damn it 🙂
OK, here is a feature description of managing formatting of new lines for item lists in different code structures.
These are for formatting lists and exist for different categories: control structures (like inside an if…), DML – for columns after SELECT, INTO, VALUES; SET inside UPDATE, and finally a different option for parameter declaration (like in functions and procedures).
4 options are possible:
1) On one line – means list items stay together on one line, no matter how long it becomes
2) One per line – that is clear (and aligned according to vertical alignment rules)
3) On one line if possible – takes into consideration the right margin (e,g. 100). If the items fit they will be formatted on one line. If that line exceeds the set margin, then the items are aligned one per line.
4) Fit – tries to have them all on one line, but if the length exceeds the margin, then they are not put one per line, but rather split to 2 lines (or more).
I don’t know how to provide screenshots for these examples. But I am ok to give you as many code as you see needed.
I think you should add your input to the forums – it sounds more than I’d want to add at this point.
I’m guessing your GIT DIFF stuff doesn’t have an option to ignore white space differences?
Does anyone use #1, really? It seems like something you’d regret the first time it hit a really long line.
When formatting manually, I suppose I use a fuzzy “on one line if reasonable” rule that is probably not straightforward to encode. If the entire SELECT list has only 3 items or so – or maybe it’s more about the visual length, say 60 characters – then I’ll put it on one line, else I’ll switch to stacking one item per line. But I wouldn’t split 30 items into 10 line of 3, and I’d guess nobody else would.
OK, I will add it.
We are using Meld for diff-ing and it does have the ignore whitespace feature. But what does this have to do? Whitespace is ignored on a “per row” basis.
i was thinking of code diff tools that could also ignore cr/lf’s and JUST compare the actual code for changes, so formatting wouldn’t be an issue
I don’t know of such tool (except for ignoring empty lines).
But the problem is not with diffing. The problem is with git. It considers every char a change. And once the formatting of a file changes, this breaks the whole Git history.
For example, the “blame” command. Just if you want to find when, who and what changed a file (or a set of lines in that file) git will give you each and every commit that changed formatting, spaces, etc.
Currently we force all our developers to use the same rules. And there is an option to enforce formatting upon compile, so they don’t forget. Then the packages, procedures, etc. are extracted with get ddl and added in Git.
With a common format rule set, only the code they changed goes to a commit.
Well, you are right, we are using mostly options 2,3 and 4.
I just pointed here all different options. And maybe logic works different way for different people 🙂
Just came across this post.
SQL Developer doesn’t understand escape for single quotes, i.e q'[Some String’s]’.
Here is the some code that gets broken on formatting:
cur_header VARCHAR2 ( 4000 ) :=
q'[SELECT e.employee_id “ID” , ]’ ||
q'[ e.first_name || ‘ ‘ || e.last_name “Name”, ]’ ||
q'[ e.salary “Salary” , ]’ ||
q'[ e.phone_number “Contact#” , ]’ ||
q'[ d.department_name “Department” , ]’ ||
q'[ l.city “City” , ]’ ||
q'[ l.state_province “State” , ]’ ||
q'[ c.country_name “Country” , ]’ ||
q'[ r.region_name “Region” ]’ ||
q'[FROM employees e ]’ ||
q'[JOIN departments d ON e.department_id = d.department_id ]’ ||
q'[JOIN locations l ON d.location_id = l.location_id ]’ ||
q'[JOIN countries c ON c.country_id = l.country_id ]’ ||
q'[JOIN regions r ON r.region_id = c.region_id ]’;
If I format the above code, it doesn’t consider q'[ as a single thing, so as per formatting preferences it can break them into separate line. Like
…
‘[JOIN locations l ON d.location_id = l.location_id ]’ || q
‘[JOIN countries c ON c.country_id = l.country_id ]’ ||
…
Thank You!
Vivek
fixed for v4.2.
Great! One more reason to eagerly wait for this release now.
Hi,
Do we have a API which can format the SQL or PL/SQL code?
I have a requirement, where my package throws out a query but all in single line, I have to format it using code.
Any help on this is appreciated.
Hey Jeff, thanks for this post!
Is ist possible, that the formatter (SQL-Dev. 4.1.2.20) has a problem with strings ending with a backslash?
Every Code after this example line:
my_string varchar(100) := ‘string with backslash\’;
will be “un-formatted” by the formatter, as there will be inserted line-breaks in strings, no indents any more…
Or is \’ recognized as an escape character by the formatter?
Currently the default is that keywords get shown in a bold font. That throws off visual vertical alignment.
Tools > Preferences > Code Editor > PL/SQL Syntax Colors
Scroll down to ‘PL/SQL Keyword’ – set Font Style to ‘Normal’
I don’t see vertical alignment being affected by this, but maybe your font face/size is different than mine.
Properly format XMLTABLE / XQUERY etc. / XMLDB functionality / operators and Native Database JSON functionality.
Or…let the new formatter leave the “string” bits and pieces untouched…
Would make my developer life a lot easier. Currently I don’t dare to use “format” on (PL/)SQL with eg. XMLTABLE statements in them because everything in that bit becomes completely rubbish/awful to read.
Perhaps offer a few “default” formatting options (e.g. default-1..n) that reflect various popular styles and allow those to be used as a starting point and tweaked where they differ from what the user prefers. The user would choose the one closest to their preference and customize as needed.
maybe, but then whose gonna determine what those popular styles are? I’d rather concentrate on getting the formatter accurate, supporting all of the various syntaxes/methods, and having it flexible enough to cover the whitespace things that drive folks nuts
I’m guessing the person who posted your exchange request example didn’t really want variable VX in uppercase and Vy in mixed case, and the lack of indentation is a side effect of the request input form and not his desired format. It’s a bit hard to tell though.
Is there any chance you could remove all options that result in tab characters, and also make everything lowercase by default? I really think TOAD and others have harmed the perception of PL/SQL as a modern programming language by making it look like COBOL.
we default to using spaces for indentation – you CAN use TABs if you want, but I think that’s…weird
you can set everything to lowercase…not sure if that’s what everyone would want by default though
I’m not convinced it’s actually possible to make tab characters work in SQL. I used them myself for years, and it took a lot of care to tab only up to the block indent level and not just any set of n spaces. I now think they only do harm and it would be an improvement not to allow them at all (make the tab key do something else, highlight them in red etc).
Regarding case, the uppercase keywords convention has become quite common so I can see why you might want the SQL Dev default to be something uncontroversial. However (as a reformed uppercaser myself) I wonder if there is an opportunity here to lead the way in fixing that.
Funnily enough, the HTML/CSS world had the same internal debate and ended up lowercase.
I’m lazy, would be cool if formatter could do all this http://www.williamrobertson.net/documents/plsqlcodingstandards.html
Thanks for the link Pp, though to be honest that document needs a bit of an overhaul as it’s currently a bit of a hodgepodge of naming, layout and rambling thoughts about the point of it all. I’ve been meaning to update it but at the rate I do things it could be a while.
I was looking at the formatting bits of your doc and I think we can conform to most if not all of it. I need to take another swing through it…maybe a blog post 🙂
1. Also, matched is not recognized as a keyword;
2. Critical for me. I would like to have the keyword uppercase, the function or procedure parameters uppercase and columns lowercase, is it possible? If you could highlight the parameters in different format that would be helpful as well;
3. This is probably a bug, but is it reproducible in my case (notice an extra space
FUNCTION IS_NUMBER
(
TEXTVALUE IN VARCHAR2
)
RETURN INT
IS
4. When I format, the pointer goes all the way down the end of the code, which is not what I expected;
5. Would it be possible to create a dbdoc template automatically? So you point to a function and click some menu item and the template is created for you, you just need to fill it in?
Some more comments:
1. litsagg and within are not recognized as keywords, btw, would it be possible to customize (add) new keywords?
I think we’re good now…
we’re pulling keywords directly out of the on-line doc sets, so it should just have EVERYTHING
Make sure that the formatter knows that ELSIF is a keyword. The present one does not.
Before:
if 1 = 1 then
null;
elsif 1=2 then
else
null;
end if;
After (setting = all keywords in caps):
IF 1 = 1 THEN
NULL;
elsif 1=2 THEN
ELSE
NULL;
END IF;
it now recognizes and treats ELSIF as a keyword, thanks
https://www.youtube.com/watch?v=nvAyulS8lmQ
“Off with his head!” – Helen Bonham-Carter in “Alice…”
If we can get a similar formatter like in TOAD, that would be awesome:)
that’s not helpful, at all
Hehehe…
Hi Jeff,
> If we can get a similar formatter like in TOAD, that would be awesome:)
well, actually, this is not a bad request. It could have been elaborated in more detail … but actually, it is quite well specified.
Certainly I am speaking about the default settings in the Toad pl/sql formatter. I typically remove the heading comment /* formatted by toad formatter … */ and then I can already “live” with the result. It is already quite compact and very readable.
What I like particularly about it is the ability to use a dynamic and compact format, i.e. when there are only a few columns in a select list (or parameter list of a function/procedure), they will fit on a single line :
SELECT oel.oel_title_billing, oel.oel_description, oel.oel_unit_price
FROM opal_pjm_ord_elements oel;
Once there are too many columns to fit into a single line … they will be wrapped:
SELECT oel.oel_title_billing,
oel.oel_description,
oel.oel_unit_price
FROM opal_pjm_ord_elements oel;
Also, I believe there are only so many different formats. The customizing is clearly overrated. I do believe there are only truly around 5 different formats (or less) that are widely used to format Oracle sql and pl/sql code.
And for these use cases there should be painless ways to implement them, e.g. at least create a custom format and make it available via download or even have the most commonly used ones selectable as a custom style already created for you (e.g. Toad Default, Oak table, etc.) .
The out-of-the-box formatting is way too verbose (too many linebreaks) from my point of view.
And BTW, the formatting in Toad is the only reason for me to still have it around … I regularly fire it up in my virtual windows machine just to format my sql code.
Just my 2 cents,
~Dietmar.
Give me your before and after formatting styles you’d like. I’ll see what we can do. Also, I’m ok if you use another tool to format your code. It just makes me feel bad if a customer needs to spend $1k on a code formatter, so I am trying to make sure we improve the formatter so that’s NEVER a possibility.
> Give me your before and after formatting styles you’d like.
This just sounds like a lot of work to cover all scenarios. Just use Toad and format any kind of code. There should be someone in Oracle having a license … and if you promise me to provide the format built into sql dev … I will gladly pay for two licenses personally myself (one for you and one for the developer) 🙂
Actually, I am a bit disappointed that even such a simple thing
1) aligning everything on the right hand side of a sql statement or
2) right aligning the keywords (select, from, where, …)
see: https://community.oracle.com/message/14205296#14205296
is so hard to do or even impossible. At least I was not able to figure it out. The “advanced” settings won’t allow me to configure that … and messing with the .arbori custom format is challenging at best.
In order to improve the situation here I would suggest to (at least) make some decent custom formats available for download, so people can try then out and use what seems to fit the best.
I would love to see an improvement here … but unless the developers start using the sql formatter themselves … I don’t see any major improvement here.
Just my 2cents,
~Dietmar.
Just use Toad…not gonna happen.
Code formatting/beauty is a subjective area. We all do use the formatter for what it’s worth.
We can look at the use cases you’ve shared so far and see what we can do, but we’re never going to just ‘copy Toad.’
>We can look at the use cases you’ve shared so far and see what we can do, but we’re never going to just ‘copy Toad.’
That’s fair. I was just pointing out Toad for two reasons: 1) the default should already be “acceptable” for most people. 2) This is the easiest way to specify the behaviour.
But I hear you. I can come up with some examples … but it will take me some time to find some free time ;).
Thank you for your support and that it matters to you!