Oracle column order (dynamically?)

Question

I have a procedure that at some point does a copy of a row from tableA + some fields to tableB like this:

INSERT INTO tableB
SELECT a.*, 'field1', 'field2'
FROM tableA a
WHERE a.id = myId

The problem came when I had to add a new column to tableA. If I add the column to tableA it's added as the last column, making this procedure invalid cause number of columns doesn't match. For this script to make sense I should add the new column from tableA to tableB. I could write all the columns by hand, but there are a lot of columns and I'm looking for something more maintainable

Is there a way to add the new column to tableB at a specific position? If it's not posible, is there a way to select every column from tableA except the new one (So I can select it as last one for tableB)? And if not, how can I automatize the column order selection/insertion?

Thanks

Answer 1

This is one of the reasons select * is often considered a bad idea in anything except ad hoc queries (and sometimes subqueries, and pivots). If the table structure changes you're a bit stuck.

You cannot select all but some columns. You also can't add a new column to a table in a specific position. You could recreate the table but that invalidates anything that refers to it, and may be too slow or use too much storage. You may be able to use the DBMS_REDEFINITION package to make that less painful and visible, if your table is suitable. But you probably don't want to do that every time the structure changes.

Another option which I wouldn't recommend is making your insert a dynamic SQL statement with the column names generated at runtime from user_tab_columns. But that has its own issues, not least that you don't get to validate the insert statement until runtime either, and you still have to figure out how the columns in the two tables align.

The sensible thing to do is take the one-time hit of exploding the * in your code. You don't have to manually type all the columns names; you can generate them from a query against user_tab_columns and cut-and-paste into your procedure. Maintenance then involves remember to add future new columns to the query, if they are required. You could potentially partially script that - e.g. you could generate a trigger that populates a history table, but probably isn't suitable in the middle of a procedure.

---

As Justin Cave pointed, Oracle 12c introduced invisible columns which would help you out here, but notice that Tom Kytes's article describes this as a workaround for "application code (waiting to be fixed!)", so you should still fix the * reference properly anyway - this just gives you space to get around to it.