The quick answer: ‘Mostly.’

Simple question of the day – how do I get SQL Developer to word wrap like Notepad does? If you don’t know what I’m talking about, then here’s a screenshot of some text before and after word wrap is applied in Notepad:

No Word Wrap

Notice the horizontal scrollbar?

Word Wrap

Now I can see all the text without scrolling.

A SQL Developer Example

Here’s some text to play with:

SELECT '012345678901234567890123456789012345678901234567890' || '12345678901234567890123456789'
FROM dual;

That’s more than 80 characters and it’s broken up over multiple ‘words.’

With mostly out-of-the-box settings in SQL Developer, you’ll see something like this when you paste into a worksheet:

What do you mean, I have to scroll?

So what’s the trick? I don’t see a ‘Word Wrap’ toggle under View or Edit. You need to configure the formatter. This is available under the Tools – Preferences dialog.

This will approximate word wrap behavior in SQL Developer

You may want to match the ‘Max Line Width‘ formatting preference with your ‘Code Editor – Display – Right Margin Column’ preference. I have done so, putting both at 80. I’ve also set the margin color to a more distinctive red.

Now you need to format the text. So, no we can’t wrap the text as you type. But if you inherit some code from a developer who has a much wider display or set of formatting preferences, you can ‘fix it’ with a single command-sequence: ‘Ctrl+F7.’

A simulated ‘word wrap’ in the SQL Developer Worksheet using the Formatter

There are quite a few formatting preferences to dig through. I love that SQL Developer will format the text to the right to ‘preview’ the behavior of the preference you’re tweaking. This saves you from having to set the preference and going back to the worksheet to try it out.

What Is the Formatter Missing?

If you have any questions about how to get the formatter to do what you want, feel free to ask them here. We’re constantly tweaking the formatter to meet your needs and are evaluating enhancements for the next several releases.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

17 Comments

  1. Mike Roeglin Reply

    I accidentally turned on a feature in the code editor that displays where the line feeds appear in the code editor. I can see how it would be helpful if you wrap code. But how do I turn this feature off?

  2. Trey O'Connor Reply

    I only want to wrap text (at the end of the screen), not do a thousand different odd things. However, when I try to do as you say, Oracle does a thousand different alterations (99.9% are completely undesirable in my scenario).

    • Then i would suggest setting up an external editor, see my post on where I show how to get it working with VI – although for what you want, even NOTEPAD would probably work.

  3. How do I change the CREATE OR REPLACE default setting in SQL Formatter:

    CREATE OR REPLACE
    PACKAGE BODY test1

    I would prefer if it was on the same line:

    CREATE OR REPLACE PACKAGE BODY test1

    • Jimmy,

      I’m with you on this, but currently that’s not something you can change. It’s on my list of things to work towards improving going forward.

      Jeff

  4. Darryl Grubbs Reply

    I tried using tags to preserve the format of the queries above, but it didn’t work. The SQL Developer formatted query has the line starting with ‘join’ at the left margin. The ‘properly formatted’ query has the line starting with ‘join’ indented to line up with ‘mtl_system_items’ in the line above. Thanks.

  5. Darryl Grubbs Reply

    By ‘properly format’, I mean that the line starting with ‘join’ should be indented instead of starting at the left margin.

    SQL Developer formatted query looks like this:

    SELECT mp.organization_code
    , msi.segment1
    FROM mtl_system_items msi
    JOIN mtl_parameters mp USING( organization_id )
    WHERE rownum < 10;

    I would like the formatted query to look like this:

    SELECT mp.organization_code
    , msi.segment1
    FROM mtl_system_items msi
    join mtl_parameters mp USING( organization_id )
    WHERE ROWNUM < 10;

    Thanks.

    • Formatting Options > Oracle > Edit

      Line Breaks
      For JOIN Statements

      If the JOIN takes multiple lines, it will indent the text afterwards, but not the JOIN line itself, like so:

      SELECT…
      LEFT OUTER JOIN xyz ON

        a = b
        AND b =
        c OR …

      But that’s not what you’re looking for. You’ll need to submit an enhancement request, sorry.

      OR

      You could try

      Indentation > Indent Main Keyword 2x

      This will give you

      SELECT…

        FROM
        INNER JOIN

      So it indents the JOIN, but it also indents the FROM and WHERE, etc.

  6. Darryl Grubbs Reply

    Hi Jeff,

    How do I get SQL Developer to properly format sql with the join keyword. The formatter always places the JOIN at the left margin.

    SELECT mp.organization_code
    , msi.segment1
    FROM mtl_system_items msi
    JOIN mtl_parameters mp USING( organization_id )
    WHERE rownum < 10;

    Thanks.

    • Please define ‘properly format.’ Give an example of how you’d like it to be formatted, and I’ll see what I can manage.

      Thanks,
      Jeff

  7. Raj Jamadagni Reply

    Jeff, does the formatter have capability to move comma in the beginning of columns in select clause? This helps in aligning as well as quickly commenting columns while testing. e.g.
    select column1
    ,column2
    from blah;

Write A Comment