Comma placement in sql scripts

It’s a truism that old habits are hard to break. Turn down the same street every day, and you’d better pay attention if you want to actually make it past that intersection without turning! One habit I feel that I need to change is my comma placement in a select statement.

Today I’ve been working on debugging a new view I’m writing to simplify some Oracle code. I’ve taken some existing code and removed the columns I don’t need, leading to something like this:

SELECT owner,
segment_name,
segment_type,
partition_name,
tablespace_name,
FROM

Running the script, I get this error:

SQL Error: ORA-00936: missing expression

This is actually more helpful than many Oracle error messages. If you look back at the select statement, you’ll see that when I deleted the final columns, I neglected to remove the comma after tablespace_name, so Oracle is looking for another column and failing to find it. If the statement had been written like this:

SELECT owner
,segment_name
,segment_type
,partition_name
,tablespace_name
FROM

then I wouldn’t be able to make that mistake. The nice thing about this format is that no line depends on the next line, and if you add another column to the end the previous line doesn’t need to be modified. I think I have a hard time getting myself to use this format because it’s not good English – but it does seem like good programming practice.

Leave a Reply

Your email address will not be published.