As of last week, Steven came back to work with Oracle, on the database tools team. I first started working with Steven at another company, doing database tools stuff, and now we’re back together again!
Ok, that’s a little dramatic sounding.
I used to work with Steven, and now I do again 🙂
I think this will lead to our tools becoming even stronger and to our users becoming even more productive.
This morning, I spent a few minutes helping Steven customize SQL Developer to make it do what it needed for him to be OK with how the code is generated, displayed, formatted, etc.
Something I find Steven to be extremely good at is identifying code aesthetics. You look at code all day. You probably dream about it, yes? So making the code as easy to read as possible is worth quite a penny or 2.
So I asked Steven…
What fonts are you using?
Lucida Console, 14pt!
Here’s what that looks like, this isn’t Steven’s code BTW:
And Steven had a few more questions?
How can I get SQL Developer to force line breaks at character position 70?
That’s a formatting option. So if you ask SQL Developer to format your code, you can tell it at what character position to end a line at.
Note, the right margin line is NOT tied to the formatter. It’s just a hard-drawn line in the editor.
This preference is located at Tools > Preferences > Code Editor > Display > Show Visible Right Margin
Note, this was missing in v4.0, but was added back (fixed) in v4.0.1.
How can I get SQL Developer treat my words the way I want, lowercase, uppercase, etc?
Formatting-wise, that’s here:
Steven also had some questions about indenting his PL/SQL code, and I’m still looking at that. But I will note that that is currently controlled in the Formatting Preferences and the Indentation sub page.
So if you see Steven around, be sure to congratulate him on re-joining the Home Team!
87 Comments
I would guess Pedro means properly as in “How to Make Your Code Look Like Steven Feuerstein’s in Oracle SQL Developer”.
Was this ever accomplished in SQLDeveloper? If so, will the config file(s) be available for download rather than everyone manually set the numerous formatting options?
I have other question about formatting:
I want to format this properly:
FROM
table 1 left
JOIN table 2 ON …
LEFT JOIN table 3 ON …
LEFT JOIN table 4 ON …
How can I do that in SQL – DEVELOPER 17.4?
Define by example, ‘properly.’
Where is SQL Formatter option in SQL Developer 4.2.0.17.089
I didn’t see it under Tools->Preferences->Database->SQL Formatter->Oracle Formatting: Edit.
Did you move it somewhere else? Thanks
View > Code Editor > Format
I think the question was now ‘how to import/export a format profile’, but rather ‘can we have a copy of the profile so we can format our code the same way?’
Maybe, that’s not what she asked though.
If you want to share your formatter options, there’s an Import and Export set of buttons on the Advanced Format page.
Very nice. Would it be possible for you or Steven to export that profile as a Code Style Profile xml file so we can easily import it? 🙂
Jeff – any chance you could address this question – I asked the same on SEPTEMBER 18, 2014 AT 12:17 AM
Cheers
NIgel
Hello – Quick question.
Is there a way to run the SQL formatted in a headless/automated way? From a command line or in a script? We develop using sqldeveloper exclusively and I would like to force coding standards when developers push code to a repository.
Thanks for any assistance!
yeah, you can use sqlcl to sdcli to format a file or even a directory of files
Fantastic – Thank you very much!
I want to make Identifies in small Letter in Pl sql code using sql developer. only option available to change the case of Oracle Keyword Letter (Like Upper or Lower). can u pls tell how how change the identifier also.
i am using sqldeveloper version : 3.2.20.9
If you upgrade, you can do this.
Hi Jeff, some helpful stuff here 😉
Any idea when 4.2 will be out? and if it will allow me to
1) Align column names in a select statement
(e.g :
SELECT a
_______, b
_______, c …
Rather than the current
SELECT a
, b
, c …. )
2) Handle OR statements within brackets ‘better’
(i.e I want :
WHERE a = b
AND ( a 30)
Rather than the current
WHERE a = b
AND ( a 30)
3) Insert a line break before a ‘SELECT’ ? The formatter becomes useless when I have several SQL statements in an editor as it conflates them all into a single massive block!
Ta
I want that too!!! (point 1.)
SELECT a
_______, b
_______, c …
WHERE table1
________,table2
___AND a = b
Does this make you feel any better Yanick?
+1 for Liam (1) and Yanick Lemire format!
Looking better, but the big thing for me is commas at the start of lines rather than the end. Commas at the end of lines are annoying when you want to comment individual lines out.
Lucky for you, that’s an option 🙂
Yay, looking forward to the EA becoming available.
It’s better for the column name and table name. How can we do that?
But there’s something to do with the WHERE statement to align conditions.
WHERE a = b
AND c = d
OR b = c;
Also, an option to left or right align SELECT, FROM, WHERE… may be interesting…
left align:
SELECT a,
________b
FROM table 1
WHERE a = b
AND c = a
right align:
SELECT a,
________b
_FROM table 1
WHERE a = b
___AND c = a
left align:
SELECT a,
________b
FROM_ table 1
WHERE a = b
AND___ c = a
(white space has been trunk!!!)
Hello Jeff,
I’ve looked all over your blog for this answer and I’m unable to find it. In TOAD, when writing PL/SQL, When a IF Statement is typed, I get a very get a very thin grey line at the start of IF and at the END IF. Do we have this feature in SQL Developer?
Thank you!
Rik
The stapling feature..no, our editor doesn’t have that.
Hello Jeff.
Is it a way to have the following formatting?
SELECT
A,
B,
C
FROM D
WHERE
(
E > F AND
G< H
);
Currently, while using indentation of 2 spaces I have also 2 spaces before brackets.
Best Regards.
Piotr
i think your whitespace got messed up, i don’t see any indentation
I will use _ as space for clearer picture
__SELECT
__A,
__B,
__C
__FROM D
__WHERE
__(
____E > F AND
____G F AND
______G< H
____);
Best Regards.
Piotr
I am looking for something like:
__SELECT
__A,
__B,
__C
__FROM D
__WHERE
__(
____E > F AND
____G F AND
____G< H
__);
where indentation do not apply for brackets after WHERE / FROM for example.
I got pretty close with v4.2 – which has a brand new formatter – and will be avail via an Early Adopter on OTN ‘soon.’
Nice.
So I am looking forward to test it
Thank you, Jeff.
In what other areas of Oracle SQL Developer we can expect new features (if that is not a secret of course)?
more stuff around the Instance Viewer, updated look and feel for Real Time SQL Monitoring, better support for the NoSQL/Big Data appliance, 12cR2 stuff, drag and drop objects around to copy from one db to another…sqlcl going GA..more stuff i can’t talk about yet 🙂
Thank you for the information, Jeff.
I am looking forward to explore these new incoming features 🙂
I can’t find how to do this really simple one:
SELECT
Field1 as “EMP_ID”
How to select a different font color(Red) for the text string “EMP_ID”?
I found everything else I need but this.
Set the foreground for ‘Default Plain Text’ in Preferences > Code Editor > PL/SQL Syntax Colors to your choice of reds.
Sounds like you’ve got the java home set to a JRE instead of a JDK.
That makes all text appear that foreground color, not just those in quotes.
I am about to do a complete reinstall of all Oracle products so that will change.
On another note I can’t thank you enough for this invaluable blog. SQLD is practically a dance partner now with all the things I can do with it. I did use TOAD a while back but xxxx dollars a seat is absurd and I’m not going to engage in the tom foolery of a “free-ish” version.
there’s a quoted strings item that will do it for single quoted text, but not double quoted
I could use some help on the sql formatter profiles.
1. when I am formatting sql in the worksheet, how can I choose from the Other Vendors profiles e.g. Access, DB2 ?
2. I see that I can ‘save as’ my profile, but how can I import a profile?
forgot to mention, I am using version 4.1.1.19
I’m trying to get the Auto Format option working version 4.1.1.19 but it never appears to do anything. I can still manually run a format but nothing happens while I’m typing like the description suggests.
the auto-format of the formatter itself happens when you load a file or open a pl/sql object
the format as you type is part of the Code Insight and only applies to lower/upper-casing of keywords and object names
That doesn’t appear to work in my version. If I browse to an object in the schema browser and open it, the object does not get auto formatted and retains it original formatting even though I’ve got the option enabled.
Yup, I see that too. Appears to be a bug.
Is there an official way I should report the bug, I found some other oddities with the formatter in general that I’d like to report as well.
I’ll log this one for you. Oddities in the formatter – best to send those to My Oracle Support as bugs or enhancement requests.
The SQL developer formatter is bad at formatting SQL and worse when it comes to PL/SQL.
Toad works a lot better in that department, why don’t standardise on Toad?
IMHO people don’t need a super-parametrizable formatter, but a formatter that does the work, and possibly in a standardized way.
Please, please, DUMP that crappy sqldeveloper formatter and implemente a simple but working and good looking one like Toad’s one.
Please, please give us some objective feedback. Provide code samples and tell us how the formatter is failing. Telling us to go work like ‘XYZ’ doesn’t really tell me anything.
there are the “de facto” standards, you know. I tried to post some code but the site apparently does not want to publish it.
A problem I’ve found is about the “exit” statement. Try to format decently “exit when (condition)”. And if you fix something, then something else is broken. The AND /OR in SQL are often aligned in and unreadable way.
I am using SQLDeveloper for a long while. It’s good overall, except for the formatter and some other quirks (why the hell I cannot attach the session I want to the current editor ?) .
>>there are the “de facto” standards, you know.
No, I do not know.
Code samples, please. Start a thread for each formatting issue you see on the OTN Community, or open an SR with MOS.
>>why the hell I cannot attach the session I want to the current editor ?
The hell if I know.
What kind of editor? You have a file open or not?
If an existing file is opened it is not possible to assign any connection to it besides the first shared connection.
If one wants to work in another session s/he is forced to copy the buffer to a worksheet tied to that session.
Thank you
The drop down connection selector for an open file works fine for me. What exactly are you doing, step-by-step? Please start with how exactly you’re opening the file, and what kind of file it is.
The things which drive me nuts about the SQL Developer formatter are:
1) AND does not right align in a SQL statement, no matter what settings one tries. Even when “Right-Align Master Keywords” is checked, then SELECT, FROM, and WHERE right alight, but for some reason AND is off in its own little world, and the A usually left aligns with the W in WHERE.
2) Too many new lines. I don’t like INTO, LOOP, and () appearing on new lines, and prefer to have the option NOT to place them on a new line.
3) Your example of how to get columns to align with the comma to the left is not viable, because changing the indentation from 2 to 7 characters changes ALL indentation, and dramatically shifts everything to the right in most package bodies. I like many others would like the default to be for column names to align within SQL Developer as follows, and it shouldn’t require manually changing the GENERAL indentation:
SELECT column 1
, column 2
, column3
4) There is no button for the current window to format like there are in other tools (PLSQL Developer, Toad), so I have to right click and then choose “Format”.
5) Select statements which follow and opening of a ref cursor are treated as strings rather than select statements and they become very difficult to read after the formatter has changed them. One can still highlight each select statement and format it individually, but it can become very time consuming when there are lots of ref cursors.
But my number 1 complaint about SQL Developer formatting is that it does not automatically place non-reserved words in lower case when I format. I have to highlight all of my package bodies, and do ctrl-” (double quote) until I get everything in lower case and then format, and only then will it display keywords in uppercase and non-keywords in lower case. It seems implicit, that if a user selects “Keywords Uppercase”, then that automatically means that they want all non-keywords to be lower case.
I am thankful that SQL Developer is a free tool, and I am also glad that you are reaching out for feedback.
Per the comments above, several people have pointed to Steven’s article which contains most commonly agreed upon SQL and PLSQL formatting standards. I see that Nigel Carr placed Steven’s Standards document in the exchange, and although “nothing is in the works” within your group to implement Steven’s standards, I’ll keep my fingers crossed that will change in the very near future.
SQL Developer formatter is likely the number one reason that people still pay for third party tools like Toad and PLSQL Developer, and if you hope to expand the SQL developer user base, then the best place to start is with the formatter following generally accepted standards as documented by Steven and used by most Oracle developers.
I’ll pass your suggestions along to our formatter dev, thanks for the explicit feedback, much more actionable than, ‘i do not like your formatter’
Not sure I’d agree with your #1 reason theory, but then at the end of the day it’s a moot point. Let’s just continue to improve and innovate as much as we can.
I look forward to seeing some of these identified SQL Developer formatter issues resolved, and at that point will become a much more loyal user, and no longer utilize clients Toad or PLSQL Developer licences.
There are some very nice features with SQL Developer, such as “Database Diff”, “Database Copy”, and “Database Export”.
I wondered if it would be possible to add one more, which would be “Database Import”. It would be similar to the Oracle APEX Data Load feature, but handle larger files. The Oracle APEX Data Load utility is almost completely unusable, as it can only handle very small files, and in the real world it doesn’t work for me 80%+ percent of the time based on the file sizes which I am attempting to load. It appears that part of the problem with Oracle APEX Data Load is that it is trying to be just a little too smart. More specifically, it appears to be the “Primary Key” step in the Oracle APEX Data Load wizard where it craps out most of the time on large csv files, so it may be helpful not to force users to have a primary key on “New Tables” created by the wizard.
Some additional features that would be nice are:
1) Possibly use Oracle External Tables feature from the users laptop, rather than requiring the file to exist on the database server,
2) Import both delimited and positional files similar to the way that the Excel wizard does, when opening a text file. When I open a text file in Microsoft Excel, I can either select the delimeter if one is detected, or click on the position (for positional files) where column breaks should occur. A good percentage of data files in the real world are positional, and SQL*Loader appears to currently be the only option and again requires access to the DB Server,
3) Provide a progress bar based on the percentage of file loaded, rather than run forever and then hang like Oracle Apex Data Load feature does for files of any significant size at all.
My guess is that a feature like this would elevate SQL Developer use markedly, as it is getting much more difficult for developers to independently do their own data loads in our increasingly SaaS, RDS, and otherwise subscription based world, which often does not allow them to place their data files on the database server to be loaded into the DB.
we have an import, just right click on your table
1) Possibly use Oracle External Tables feature from the users laptop, rather than requiring the file to exist on the database server,
think about it, you’re asking Oracle to create an external table, and you’re telling it that the file is on your computer
every time someone selects your table, the oracle server has to go read the data off of your laptop
you can technically do this, i think. you’d just use something like Samba and add a directory on your server that reads directly from your laptop – i just wouldn’t recommend it
4) You can set a hot key .. I think it defaults to Ctrl-f7? But you can alter it to whatever you want.
Your comment is not constructive at all. Also, “Toad” is not a “standard formatter”, in fact there is no such thing as a “standard” formatter.
I am happy to notice that the upcoming version 4.2 has a brand new formatter.
I was a bit rude, I am sorry, but the current formatter, although not unusable, is not good enough for evident reasons.
Markojaric, above, said, and I agree, that “formatter is likely the number one reason that people still pay for third party tools like Toad and PLSQL Developer”.
I tried the new version and it looks a huge step forward, but it has at least a quite serious bug.
If you try to format a plsql program containing sql string with escaped single quotes (i.e. a couple of consecutive single quotes) , one of the two is removed and the code is screwed.
Thank you.
That’s been fixed
I am trying to use the 4.2 EA version available from the Oracle site. As far as I can see there are no updates available and with the current software I have the bug.
please try to format
declare
q varchar2(1000);
begin
q := ‘select ”A” from dual’;
end;
That’s been fixed for our next update, stay tuned.
Just a question, it doesn’t seem like formatter (in 3.1.07) allows me to do this:
(e.g. the braces in the procedure line)
PROCEDURE foo(
__param1 IN INTEGER,
__param2 IN TIMESTAMP,
__param3 IN VARCHAR2
)
IS
__iInt INTEGER;
__sString VARCHAR2(200) ;
BEGIN
/** magical stuff goes here **/
END;
Also, the formatter seems inconsistent in terms of how it treats line-breaks and braces in the FUNCTION/PROCEDURE lines compared to what it does in the body.
we’ve put in lots of fixes and one-off formatting requests into the product since 3.1 – can you upgrade?
I just upgraded to the latest 4.0.3.16.84.
Doesn’t seem like those particular formatting options are available. The sample output is still inconsistent (I’ll add snippets):
FUNCTION testfunction(
____p_column12 IN VARCHAR2)
__RETURN VARCHAR2
IS
BEGIN
__…
__SELECT 1
__INTO var
__WHERE EXISTS
____(SELECT col1
____, — first field
____longcol2
____) ; /* also not sure why there’s an extra space between the brace and semi-colon here… */
…
Hi Jeff,
good to see the formatting options can be tweaked.
There’s one issue with the formatter, though, that keeps us from using it in the first place. I can’t figure out how to make a column list align like this (underscore = space):
_SELECT_column1
______,_column2
______,_column3
______,_column4
___FROM_table
The closest result that the formatter produces is:
_SELECT_column1,
__column2,
__column3,
__column4
___FROM table
where all column names start at text position 3. I don’t insist on my comma preference, but it seems impossible to left-align column names with the first line (i.e. starting at text position 9)
Overlooked something?
Sincerely,
Andreas
Going from
column,
to
, column
is easy. Just set in the preferences, Line Breaks ‘before comma’ on.
Getting the alignment is possible too. manually set your indentation to 7 spaces. And then after format, I get something like this
This assumes the first thing after your select is a column name, not a hint or commment. So most of the time it should get you very close to what you want.
I’m referring to the naming conventions on pl/sql variables and the like. Steven wrote an article on it here http://www.toadworld.com/platforms/oracle/w/wiki/8245.plsql-standards.aspx and Trivadis have provided a detailed guide also mentioned in Steven’s article.
This was added in the wrong place. Please delete.
Cheers
Nigel
Hi Jeff,
Great news that Steven has joined SQLDeveloper dev team.
Once he has configured SQL developer to format how he wants it to will SQLDeveloper ship it so it can be imported?
Are there any plans to include a tool for validating naming conventions that could comply with standards such as Trivadis PL/SQL & SQL Coding Guidelines.
Cheers
Nigel
Nigel,
Steven’s not on the SQL Developer team-proper, but he is working with us to improve the formatter. For naming conventions, have you seen the naming standards, glossaries you can implement in the Data Modeler?
I’m referring to the naming conventions on pl/sql variables and the like. Steven wrote an article on it here http://www.toadworld.com/platforms/oracle/w/wiki/8245.plsql-standards.aspx and Trivadis have provided a detailed guide also mentioned in Steven’s article.
Toadworld…sounds like a weird place.
Yeah, we don’t have anything in the works for that – but feel free to suggest it on our Exchange.
Thanks Jeff I have raised it in exchange.
My original post also asked if Steven’s config could be supplied so it could be imported and therefore formatting options would have a standardized feel.
Cheers
Nigel
I prefer “crisp” font.
Is monospaced and the ZERO slashed.
.
http://www.g0hwc.com/ham_radio_fonts.html
.
Bye
Alvaro
Hi,
nice to see that you like the SQL Formatter. The SQL formatter used in SQL Developer is based upon the SQLinForm SQL Formatter (www.sqlinform.com) The new version has additional options as allowing profiles for different formatting styles. Maybe you want to give it a try?
Regards
Guido
Is it possible to have completion insight set to “Lower Keywords” without changing the identifiers? In our project we use a camelCase writing for identifiers and like to keep that.
Jeff,
Would it be possible in a future release to have muliple, named, formatting profiles.
As everyone knows there are as many ‘right’ ways of formatting as there are developers.
However in every shop there is only one proper way of formatting: the one the client demands.
With named formatting profiles I can take a piece of code I need to work on, format it the only right way by choosing profile “my_brilliant_formatting”.
When I’m ready to send my baby into the world I select “customers_stupid_formatting”, and everyone’s happy. Until they run into my correctly formatted bugs, that is.
I believe that exists. You can import your client’s formatting preferences by going to Preferences->Database->SQL Formatter and using the import button. Then drill down one more level to Oracle Formatting. At the top is a drop down menu labeled “Profile” which should now list your current format along with your imported client’s format for you to switch between.
Heh, maybe Steven will give your team a push to expand and improve your code formatter. I, for one, couldn’t configure it to suit my needs, so I end up not using it at all, except to sort out some external garbage queries. Using an IDE without using its formatter is… well… you know.
Your formatter provides many options, but they don’t seem to cover everything, unlike say jalopy for Java code. For example, how do I align all master keywords on the right, INCLUDING and/or operators in the “where” clause, and at the same time align everything else beyond master keywords, including multiline, to the left of the column that master keywords are right-aligned to? How do I indent subsequent clauses in the brackets? See http://pastebin.com/TpApGXF8 for a small example, and that’s just the simplest case, without decodes or cases or subqueries – what do I do about 50+-line what-a-mess-tier queries?
Also, why setting “use tabulator” messes up right-aligning so much?
What in the world is large/small/customized SQL? Why can’t I paste my own code in when playing with formatter options, to see what I’ll actually be getting from my code, rather that example you provided? Can we help you build an omnipotent formatter that other tools would be jealous of? =)
Yes, he’s already giving feedback.
The feedback I usually get on the formatter is along the lines of ‘make it better’ – with little to no concrete examples. And of course ‘correct’ formatting is a subjective issue. But I do thank you for some of your specific feedback.
As for what in the world is large/small SQL – that’s the length of a statement at which the formatter decides to apply formatting. So do you want to format a query that’s only 50 characters and would easily fit on a single line? I talk about this a little bit here
I’ll take a look at your alignment issues and see if there’s a way to make that happen with the current tech.
It’s great that feedback flows in already! Hope to see great formatting soon =)
As for the feedback from customers… It is equally hard for us to give it, as it is hard for you to guess what we need.
For languages like C++/Java/etc there are known ANSI/GNU/other indentation standards, abiding which is considered a good practice, and even then, there is much diversity (I prefer Whitesmiths indentation style, except not breaking else if statements).
Yet I didn’t ever see one for PL/SQL – or, to be frank, for any SQL. So it’s hard for us to formalize certain formatting rules as “right”, because we must be sure we are doing it “right” ourselves. So we can only try to make formatter match our subjective needs for “pretty code”, but it seems stupid to throw in service request or question in forums, asking to “make the formatter make the code pretty”.
If you want a more concrete feedback from multiple people, how about starting a “sticky” thread an the OTN forums, providing some code and asking people to show how they would format it? After gathering enough examples, it’ll be easier to formalize more rules to suit everyone’s needs, and maybe even throw together some predefined rule sets.
Oh wow, glad to hear both you and Steven are there. This can only mean great things to come for us developers!
I don’t know whether this is caused by the Windows platform (in my case Windows 7, latest version of SQL Developer), but a (to me) annoying little thing about Lucida Console is that white-space doesn’t align the same way that is does in other fonts. If I cut-and-paste the following into a worksheet:
SELECT dummy — this is a comment
, dummy — this is also a comment
FROM dual;
then the columns and comments are aligned if I have chosen Courier New, but not in Lucida Console…
(sorry, html ate the whitespace: there are five spaces before the comma, and four spaces before the ‘dash-dash’ start of the comment)
Further investigation seems to show that is it not Windows in itself that causes this behaviour: This issue isn’t seen in Notepad.