How to Make Your Code Look Like Steven Feuerstein’s in Oracle SQL Developer

thatjeffsmith SQL Developer 84 Comments

Tell Others About This Story:

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:

"It is a variant of Lucida Sans Typewriter with smaller line spacing, and added WGL4 character set."

“It is a variant of Lucida Sans Typewriter with smaller line spacing, and added WGL4 character set.”

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.

line_breaks2

Note, the right margin line is NOT tied to the formatter. It’s just a hard-drawn line in the editor.

See where your code will eventually be aligned with the visible right margin

See where your code will eventually be aligned with the visible right margin

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:

Keywords Uppercase it is!

Keywords Uppercase it is!

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!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 84

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

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

        1. thatjeffsmith Post
          Author
  2. 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? ๐Ÿ™‚

    1. Jeff – any chance you could address this question – I asked the same on SEPTEMBER 18, 2014 AT 12:17 AM

      Cheers

      NIgel

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

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

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

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

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

          1. left align:
            SELECT a,
            ________b
            FROM_ table 1
            WHERE a = b
            AND___ c = a

            (white space has been trunk!!!)

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

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

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

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

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

          3. thatjeffsmith Post
            Author

            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 ๐Ÿ™‚

          4. Thank you for the information, Jeff.

            I am looking forward to explore these new incoming features ๐Ÿ™‚

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

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

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

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

    1. thatjeffsmith Post
      Author

      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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

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

        1. thatjeffsmith Post
          Author

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

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

          2. thatjeffsmith Post
            Author

            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.

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

        1. thatjeffsmith Post
          Author

          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.

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

          2. thatjeffsmith Post
            Author
          3. thatjeffsmith Post
            Author

            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

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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?

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

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

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

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

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

  18. 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? =)

    1. thatjeffsmith Post
      Author

      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.

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

  19. Oh wow, glad to hear both you and Steven are there. This can only mean great things to come for us developers!

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

      1. (sorry, html ate the whitespace: there are five spaces before the comma, and four spaces before the ‘dash-dash’ start of the comment)

      2. Further investigation seems to show that is it not Windows in itself that causes this behaviour: This issue isn’t seen in Notepad.

Leave a Reply

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