6

When I use a select * statement in SQL developer, the order of the columns returned is not the order defined in the table. If I view the table itself in the connections pane, under the columns tab the columns are in the correct order and have the correct column id assigned. When anyone else runs a query on the table using a different pc, the columns are displayed in the correct order. This leads me to believe that this is a setting in my sql developer. If I'm correct, how can I change this.

EX. The columns are in this order according to their column id in the columns tab. This the left to right order displayed when a select * is performed on this table from any other pc:

1. MESSAGE_ID
2. SOURCE_ID
3. EVENT_ID
4. WHEN_CREATED
5. CONTAINER_ID
6. CONTAINER_TYPE
7. WAVE_NUMBER
8. LOCATION
9. DEST_LOCATION
10. ITEM_NAME
11. BATCH_NUMBER
12. BARCODE
13. CONTAINER_QUANTITY
14. CUBE_VOLUME
15. ORDER_ID
16. PICK_STATUS
17. PICK_PUT_LOCATION
18. ITEM_QUANTITY
19. PICKED_QUANTITY
20. REQUIRED_QUANTITY
21. MOD_DATE
22. USER_ID

However, when I select * from this table in sql developer on my pc, this is the order they appear from left to right:

1. WAVE_NUMBER
2. LOCATION
3. ITEM_NAME
4. BATCH_NUMBER
5. BARCODE
6. CONTAINER_ID
7. CONTAINER_QUANTITY
8. CUBE_VOLUME
9. ORDER_ID
10. PICK_STATUS
11. PICK_PUT_LOCATION
12. ITEM_QUANTITY
13. PICKED_QUANTITY
14. REQUIRED_QUANTITY
15. MOD_DATE
16. USER_ID
17. MESSAGE_ID
18. SOURCE_ID
19. EVENT_ID
20. WHEN_CREATED
21. CONTAINER_TYPE
22. DEST_LOCATION
Moudiz
  • 7,211
  • 22
  • 78
  • 156
philipmasonno1
  • 71
  • 1
  • 1
  • 3
  • A good practice is to mention the columns when selecting them , however check the below lings [DBMS_REDEFENITION](https://docs.oracle.com/html/B14258_02/d_redefi.htm) and this SO [Anser](http://stackoverflow.com/questions/4939735/re-order-columns-of-table-in-oracle) – Moudiz Feb 06 '16 at 16:04
  • @Aleksej the link you gave is broken – Moudiz Feb 06 '16 at 16:09
  • @Moudiz thank you for the reply, however this is a quick reference table where users will not know what to select. I previously referenced this before in the initial question, but I'm the only one who gets the columns out of order. That's what makes me think that this has something to do with my sql developer exclusively. – philipmasonno1 Feb 06 '16 at 16:30

2 Answers2

20

Right-click on your column header and select 'delete persisted settings'

I'm guessing at some point you or someone else on your machine re-ordered the columns.

This will reset any stored order in SQL Developer.

Like so...

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • What version of developer are you using? when i right click, i don't have that option. I only have the auto-fit options. – philipmasonno1 Feb 06 '16 at 18:20
  • that's version 4.1.3 - but most of those context menu items have been there for years. If you're not seeing those items - this is from a Oracle query result in a SQL worksheet, something is 'borked' with your install. try deleting the systemX.Y.Z folder. On Windows, this in is the $USER/AppData/Roaming folder – thatjeffsmith Feb 06 '16 at 20:20
  • That may be the problem of why i don't see it. I'm using a company laptop that has 1.5.5. Does anyone know how to clear "delete the persisted settings..." in an earlier version? – philipmasonno1 Feb 06 '16 at 21:27
  • 2
    Your company won't mind if you get software from 2016 vs 2006. Just get the zip from OTN, no installer required. – thatjeffsmith Feb 07 '16 at 16:04
0

Add a space to the query to break the match. I ran into this issue with Oracle Database Express Edition Release 11.2.0.2.0 because I redefined a table with a new column order, and I had trouble pulling the new sort order with SQL Developer version 1.1.3 build MAIN-27.69 which is included with the database software. Adding a space to my query did the trick.

SELECT * FROM my_tab;
SELECT *  FROM my_tab;
durette
  • 353
  • 1
  • 12