How to Re-Order Column Position of a Table

  • by

One developer reported that some statements like the following failed to complete.

SQL> insert into finapp.invoices_history select * from finapp.invoices_month where invoice_date < sysdate -30;

The statement tried to move some data from a present (hot) table to a history (cold) table.

After investigation, we found that it's because the column position of the target table is a little different from the source table, although the column names are the same.

Source Table -> C1, C2, C3
Target Table -> C1, C3, C2

In such situation, user process will retrieve columns from the source table with their defined order COLUMN_ID in ALL_TAB_COLS, then insert into the target table. Value mismatch error like ORA-01401 usually follows.

Solutions

Our goal is to make the order of target table match the source table's. There're several ways to fix the problem.

1. Explicit Column List

The first solution is very obvious, just explicitly list column names instead of a wild card in SELECT statement to match the column order of the target table.

SQL> insert into finapp.invoices_history select c1, c3, c2 from finapp.invoices_month where invoice_date < sysdate -30;

Or vice versa, specify column order of the source table in INSERT INTO statement.

SQL> insert into finapp.invoices_history (c1, c2, c3) select * from finapp.invoices_month where invoice_date < sysdate -30;

2. Create New Table

If developers don't want to change the statements, we should re-order column position of the target table so as to conform to the source table.

We can create a new table with new column order to take over the data from the original table.

Rename Table

SQL> alter table finapp.invoices_history rename to invoices_history_1;

Create Table As Select (CTAS)

SQL> create table finapp.invoices_history as select c1, c2, c3 from finapp.invoices_history_1;

Then add indexes and constraints to the new table.

3. Create New View

We can also create a new view with new column order to pretend that we have re-ordered the column position.

Rename Table

SQL> alter table finapp.invoices_history rename to invoices_history_1;

Create View As Select (CVAS)

SQL> create view finapp.invoices_history as select c1, c2, c3 from finapp.invoices_history_1;

The best thing is that there's no need to add indexes or constraints.

Leave a Reply

Your email address will not be published. Required fields are marked *