Buradaki soru da çok güzel cevabı da paylaşmak istedim ,
http://forums.oracle.com/forums/thread.jspa?messageID=1383006
I have seen this question a couple of time on the net over the last little while. I posted the answer below, and thought I might as well put it on the blog as well. The thread continued on after the initial question to include a bunch of more detail that made my answer not the correct answer. I guess I should have simply asked “Why” instead of trying to figure out what the person really wanted.
Unfortunately I have had to do this in the past:
Don’t drop your table of course, but I wanted this to be complete:
0 1 2 3 4 5 6 7 8 9 | DROP TABLE REORDER; CREATE TABLE REORDER (COLUMN4 NUMBER ,COLUMN3 NUMBER ,COLUMN2 NUMBER ,COLUMN5 NUMBER ,COLUMN1 NUMBER ,COLUMN6 NUMBER ); |
Now if you select it the columns come up in the order it was created
0 | SELECT * FROM REORDER; |
You can simply select the columns in the order you want:
0 | SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER; |
You can create a view that the users can use if you wish:
0 | CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER; |
0 | SELECT * FROM ORDERED_REORDER; |
clean up
0 | DROP VIEW ORDERED_REORDER; |
I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn’t do this without some thorough testing.
change the name of the table
0 | RENAME REORDER TO ORIG_REORDER; |
create a view to look like the original table
0 | CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER; |
Now the view looks the way you want:
0 | SELECT * FROM REORDER; |
You can insert etc:
0 1 2 | INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6) VALUES (1,2,3,4,5,6); COMMIT; |
Some apps won’t allow this, they need tables and won’t allow views.
Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn’t a better way to handle the request.
