SQL Developer Quick Tip – Leading Comma Formatting

Some folks find this hard to read

SELECT a,
       b,
       c,
       d,
       e
FROM TABLE;

If your computed column code or column names are very long, they’ll often trail off the edge of the viewable screen. If you’re missing a comma somewhere, you’re bound to run up against an ORA-. You want to know right away WHERE you’re missing the separator.

I covered this ‘debate’ earlier in ‘Trailing or Leading Commmas.’ If you would like to get your code to FORMAT with leading commas in SQL Developer, here’s how to do it.

• Open the SQL Developer Preferences
• Expand the Database Page
• Select SQL Formatting – Oracle Formatting
• Hit the ‘Edit’ button
• Under ‘Line Breaks’, toggle ‘Before Comma’
• Format your code, and voila!

But Wait, How Do I Invoke the Formatter?

Mouse-right-click in the worksheet or Ctrl+F7

SELECT name 'Parameter'
,
  VALUE 'Value'
,
  update_comment 'Comment'
,
  DECODE(TYPE,1,'Boolean',2,' String',3,'Integer',4,'Parameter file',5,'Reserved',6,'Big integer') 'Type'
,
  description 'Description'
,
  DECODE(ISDEFAULT,'TRUE','No','FALSE','Yes') 'Modified'
,
  DECODE(isinstance_modifiable,'TRUE','Yes','No') 'Dynamic'
,
  DECODE(ISBASIC,'TRUE','Yes','No') 'Basic'
FROM V$SYSTEM_PARAMETER Paramter
WHERE name LIKE '%process%'
ORDER BY 1;

–formatted with Oracle SQL Developer Version 3.1.05

Thanks @bigjim for the reminder! If you think trailing commas are better, take it up with Jim 🙂