Column Settings

[ibrahim2008]

In my table,Let's say EMP,there are three columns EMPID,ENAME,DEP TNO,LOCATION(in this order),but i want it to be EMPID,DEPTNO,EN AME,LOCATION(pe rmanently).
Can it be possible,please help me.

Regards,
Ibrahim

[Pilgrim333]

Hi,

The way to do that is, rename the table, create a new table with the right column order, populate the table with the data from the renamed table and then drop the renamed table. Remember to take a look at the grants, indexes etc.

I don't know of any other way, if anyone else knows one, i am curious.

Pilgrim.

[amitpatel66]

If you are looking at just selecting the column in that order then you can do that with simple SELECT statement itself. Something like

1. SELECT <your columns> FROM table_name will give you the data in the order in which you sepecified the columns.

2. You can also look at taking the data back up in temp table and renaming the column names and its data types accordingly after truncating the table data. Then restore the data again from temp table

3. Simple option -> Three step process:

CREATE TABLE temp_emp AS SELECT empno,deptno,en ame,location FROM emp

Drop table emp

Rename temp_emp TO emp

[ibrahim2008]

I know all this what you all have described,but any other method.


Regards,
Ibrahim

[amitpatel66]

No you DO NOT have any way of reordering table columns after creating a table

[Saii]

In 10g, try experimenting with alter table options like rename,drop unused. I haven't tried but i think it should work.