how to add a column in required position in the table in Oracle without using on fly table creation?

Before considering any method of adding a column in the required position in the table without using on fly table creation you should be asking a more important question. "Why?" Why does the new column have to be in a specific position?


Even if there is a good answer to that question there is another question to ask - is it safe to rely on that column always being in that position? By that we mean do the applications using this table in your Oracle database assume that this column will be in a specific position? If they do, we would argue that that is a dangerous assumption and if they don't, it brings us back to the first question.

In Oracle, columns don't need to be in a particular order in a table. If you specify the columns required in your SQL queries by using something like

SELECT col1,col2,col3 FROM mytable

rather than

SELECT * FROM mytable

then you can fetch the columns in any order you like.

You can do the same with INSERT statements.

INSERT INTO mytable(col3,col6,col1,col87,col2) VALUES (val3,val6,val1,val87,val2)

The above is safer than

INSERT INTO mytable VALUES (val3,val6,val1,val87,val2)

because you cannot be certain that the order of the columns will never change and even if you could, new columns could be added and so unless default values were specified for these new columns, the code above would fail at runtime.

However if you have a good reason for adding a new column in a specific location in the table and you don't want to do on the fly table creation then you're left with only one choice - add the column at the end of the table, rename the table and then create a view with the original table name that selects the columns from table in the order you want.

You might think that doing that will prevent tour applications from being able to update data in the table or insert new data into it. Fortunately that is not the case as Oracle allows us to INSERT and UPDATE through a view and this would be completely transparent to your applications.

You might also need to use an Oracle PL/SQL INSTEAD OF trigger on the view for updates and inserts. The code in these triggers (one for update and one for insert) would be run instead of the normal insert or update on the table.

Let's look at an example using the employees table in the hr schema that comes with the sample database.

First we create a view on our employees table as it is.

CREATE OR REPLACE VIEW ev AS SELECT * FROM employees


Our view looks like this:-

Name            Null     Type
-------------- -------- ------------
EMPLOYEE_ID    NOT NULL NUMBER(6)
FIRST_NAME              VARCHAR2(20)
LAST_NAME      NOT NULL VARCHAR2(25)
EMAIL          NOT NULL VARCHAR2(25)
PHONE_NUMBER            VARCHAR2(20)
HIRE_DATE      NOT NULL DATE
JOB_ID         NOT NULL VARCHAR2(10)
SALARY                  NUMBER(8,2)
COMMISSION_PCT          NUMBER(2,2)
MANAGER_ID              NUMBER(6)
DEPARTMENT_ID           NUMBER(4)


Next add a new column to the employees table.

ALTER TABLE employees ADD (newcol VARCHAR2(30));


After this our employee table would like like this:

Name            Null     Type
-------------- -------- ------------
EMPLOYEE_ID    NOT NULL NUMBER(6)
FIRST_NAME              VARCHAR2(20)
LAST_NAME      NOT NULL VARCHAR2(25)
EMAIL          NOT NULL VARCHAR2(25)
PHONE_NUMBER            VARCHAR2(20)
HIRE_DATE      NOT NULL DATE
JOB_ID         NOT NULL VARCHAR2(10)
SALARY                  NUMBER(8,2)
COMMISSION_PCT          NUMBER(2,2)
MANAGER_ID              NUMBER(6)
DEPARTMENT_ID           NUMBER(4)
NEWCOL                  VARCHAR2(30)


Next we create an INSTEAD OF INSERT trigger on our view so that when we insert into the view the rigger fires and writes the columns to the employees table in the right order.

CREATE OR REPLACE TRIGGER ev_ins
INSTEAD OF INSERT ON EV for each row
BEGIN
INSERT INTO EMPLOYEES VALUES (
:new.employee_id
,:new.first_name
,:new.last_name
,:new.email
,:new.phone_number
,:new.hire_date

,'SH_CLERK' -- job_id

,:new.salary
,:new.commission_pct
,:new.manager_id
,:new.department_id
,:new.job_id -- newcol);
END;


Now when we issue an INSERT statement on the view like this:

INSERT INTO ev VALUES (999,'jo','sm','js','09',sysdate,'IT_PROG',150000,0.15,102,50)


the trigger fires instead and changes the value that would be assigned to the column job_id from 'IT_PROG' to 'SH_CLERK' and assigns the supplied job_id (in this case 'IT_PROG') to the new column newcol. This gives us the following row in the table:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID NEWCOL
999 jo sm js 09 26/09/13 SH_CLERK 150000 0.15 102 50 IT_PROG

This shows that we successfully changed the insert statement to supply a value for the new column and change what would have been assigned to job_id column.

However just because you can do something, it doesn't mean that you should do it. Using an INSTEAD OF trigger in this way would make the code harder to understand and therefore harder to maintain as the trigger is invisible to the application and would be hidden from any developers looking at the source code as the trigger is obviously stored separately in your Oracle database.

For more help with developing applications for Oracle why not take a course with one of our partners. In conjunction with them we can offer training in the UK, the US and on-line. Click here for more details.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.