Problem solve Get help with specific problems with your technologies, process and projects.

Can I specify Oracle column order in my database table?

Oracle expert Brian Peasland explains whether you can specify Oracle column order in your Oracle database and gives example column order SQL statements in this expert tip.

Why is it not possible in Oracle to specify the location for any newly added column? Why does it always go last? Is there any option in the upcoming versions of Oracle?
In relational database theory, there is no concept to the order of rows or columns in any table. A table is a "set" of data and there is no order in a set. As such, there is no ANSI or ISO SQL standard which defines an ordering of columns or rows. So RDBMS vendors are left to implement these sorts of things however they want. Oracle likes to put the newly added column "last". This is just the way Oracle decided to handle adding a new column.

Since column order is meaningless in relational database theory, it is always a good idea to explicitly specify the order you want columns returned in your SELECT statement. Instead of:

SELECT * FROM table;

One should always do:

SELECT column1, column2, … , columnN FROM table;

This way, you are explicitly stating the order the columns appear in the resultset. There are many reasons you want to explicitly state the columns in the order you want to see them in your application's SQL statements. Those reasons include, but are not limited to:

  • A database reorg cannot guarantee the order of the columns will stay the same. Explicitly denoting the column order in the SELECT clause will guarantee that your application does not break.
  • If someone adds a column to the table, the * in the SELECT clause can break some applications that won't know how to handle the new column. If you enumerate your columns in the SELECT clause, your application code will not break when a column is added to the table.
  • While you may like the column order of the table (if it exists), some other part of the application may like the columns in another order. Explicitly enumerating those columns in the SELECT clause guarantees that your resultset returns columns in the order you require.

As far as I know, Oracle will not be implementing any feature to guarantee column order in their databases.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.