Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Formatting your SQL Queries, Indented Predicates on New Lines

1 Min Read

Someone asked in our forums how to format their code the way they want it.

Specifically, they have code like so:

SELECT t1.col1 ,
  t1.col2 ,
  t1.col3 ,
  t1.col4 ,
  t1.col5 ,
  t1.otp ,
  t2.fid ,
  t2.pid
FROM table_1 t1 ,
  table_2 t2 ,
  table_3 t3
WHERE t1.rid = in_rid
AND t1.col5  = t2.lid
AND t1.col1  = t3.loid
AND t1.atp NOT LIKE 'ABC%'
AND NOT EXISTS
  (SELECT t4.*
  FROM table_4 t4,
    table_5 t5
  WHERE t4.tid  = t5.tid
  AND t5.active = 'Y'
  AND t4.flag   = CC_FLAG
  AND t4.anw   IS NULL
  )
ORDER BY t1.col3;

They don’t want this:

Hard to read?
Hard to read?

The user doesn’t want the first predicate on the same line as the ‘WHERE’ keyword, and doesn’t want the additional predicate clauses to be on the same vertical position as the WHERE keyword as well.

Thankfully there are 2 Formatter preferences that make this easy to fix.

So the AFTER FORMAT looks like this:

SELECT
  t1.col1 ,
  t1.col2 ,
  t1.col3 ,
  t1.col4 ,
  t1.col5 ,
  t1.otp ,
  t2.fid ,
  t2.pid
FROM
  table_1 t1 ,
  table_2 t2 ,
  table_3 t3
WHERE
  t1.rid      = in_rid
  AND t1.col5 = t2.lid
  AND t1.col1 = t3.loid
  AND t1.atp NOT LIKE 'ABC%'
  AND NOT EXISTS
  (SELECT
    t4.*
  FROM
    table_4 t4,
    table_5 t5
  WHERE
    t4.tid        = t5.tid
    AND t5.active = 'Y'
    AND t4.flag   = CC_FLAG
    AND t4.anw   IS NULL
  )
ORDER BY
  t1.col3;

predicates2

The Preferences

Neither of these are on by default.
Neither of these are on by default.

Not sure if you’re playing around with the ‘right’ preference? The code preview to the right updates AS you tweak the preference. That means you don’t have to guess.

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.

Related Posts

9 Comments

  2. Kevan Gelling Reply

    This might format better

    SELECT  t1.col1 ,
        t1.col2 ,
        t1.col3
FROM    table_1 t1 ,
        table_2 t2
WHERE   t1.rid  = in_rid
  AND   t1.col5 = t2.lid
  AND   t1.col1 = t3.loid
    • Frank Feng

      People work on PL/SQL and complex queries. I just tried the number 7, and the sample code in the right-side panel looks just weird.

      One way to think about the SQL formatting is to separate it into two parts: the operators (main keywords) and operands (expression, lists etc.). The operators part will be at left side, either left align or right align; the operands part will be at right side, always left align. For the long keywords, like group by, just aligh the ‘group’ at the left side, and the ‘by’ at the right side.

  3. Kevan Gelling Reply

    Can SQL Developer format SQL this way – with 1st column / table / condition on same line as the SELECT / FROM / WHERE and the subsequent lines aligned to the column / table / condition ?


    SELECT t1.col1 ,
    t1.col2 ,
    t1.col3
    FROM table_1 t1 ,
    table_2 t2
    WHERE t1.rid = in_rid
    AND t1.col5 = t2.lid
    AND t1.col1 = t3.loid

  5. Geof Reply

    Ah, thanks. I noticed that I had two profiles in the Profile drop down, “Old Preferences” and “SQL”. Once I changed the preferences for the SQL profile the formatting came into effect.

  6. Geof Reply

    Jeff, I can’t find these preferences under Tools, Preferences in version 4.1

    • thatjeffsmith Post Author

      Preferences – Database – SQL Formatter – Oracle Formatting – hit the ‘Edit’ button

Write A Comment