-3

I got a tables MPDAT_ALT with columns R_ID, CUSTOMER, ORDER_DATE, ORDER_ID. CUSTOMER has column_id '1', R_ID '2', ORDER_DATE '3', ORDER_ID '4'. But the data is shown with the the following column order: R_ID, CUSTOMER, ORDER_DATE, ORDER_ID. Why is that? I created the table MPDAT_ALT by executing the SQL statement:

CREATE TABLE MPDAT_ALT as (SELECT CUSTOMER, R_ID, ORDER_DATE, ORDER_ID from MPDAT)

The table MPDAT has column R_ID first, then CUSTOMER, ORDER_DATE and ORDER_ID. Why doesn't MPDAT_ALT show me the data with the column order the column ID's imply?

Ben C.
  • 291
  • 2
  • 11
  • You're referring to two very similar tables, maybe you can show the query you use and (a small amount of) output, run against both tables. And also the query you're using to get the column IDs - that may be against the wrong table too. Using `select *` and relying on the column order in the data dictionary is considered bad practice anyway. If you listed the columns in the select list you wouldn't be able to be surprised. – Alex Poole Mar 31 '16 at 15:05
  • I should have added something... The table MPDAT_ALT existed before and the column order was the same as the one from MPDAT. I dropped MPDAT_ALT then and created it again but this time with the new column order. I can check the column ID's directly in the SQL Developer. For me it's just important how the data is presented when I click on data in the SQL Developer. – Ben C. Mar 31 '16 at 15:21
  • Well that is kind of important. Have you refreshed the SQL Developer view of the table? I'd guess you clicked the little refresh symbol on the 'Columns' tab but not on the 'Data' tab, which would leave them (apparently) out of step. Or you can close and reopen the table... – Alex Poole Mar 31 '16 at 15:27
  • Yes, I refreshed also the Data tab. May DBMS_REDEFINITION be a step towards a solution? – Ben C. Mar 31 '16 at 15:28
  • Since it sounds like a temporary display discrepancy you can easily fix, no, that would be overkill. Why does the column order matter anyway? Any actual queries should be explicitly listing the relevant columns in the required order. Creating one table from another sounds strange here anyway - it seems like you really want the _ALT table to be a view that just changes the column order; but even that isn't necessary if your queries are written properly. – Alex Poole Mar 31 '16 at 15:31
  • OK, then close the MPDAT_ALT tab and reopen it from the connection's object navigator - that does seem to keep the old (pre-drop/recreate) column order if you just refresh it, at least in version 4.1.3. Or quit and relaunch SQL Developer, if all else fails. – Alex Poole Mar 31 '16 at 15:34
  • It's more like an aesthetical requirement. Is there no possibility to set the presented order in the data tab like the order given by the column id's? – Ben C. Mar 31 '16 at 15:37
  • Yes - close it and reopen it. – Alex Poole Mar 31 '16 at 15:38
  • Even after reopening, the SQL Developer didn't adjust the order... – Ben C. Mar 31 '16 at 15:40
  • In 4.1.3 that does change the order for me; as does right-clicking the Tables list and choosing refresh from there. If neither of those work, which version are you running? You may have to quit and relaunch SQL Developer. – Alex Poole Mar 31 '16 at 15:48

1 Answers1

0

Summarising from comments: you had an existing MPDAT_ALT table which you had open in the object viewer (by selecting it from the Connections window's Tables list). While that table tab was open you dropped and recreated the table from a worksheet. You then went back to the open MPDAT_ALT tab and refreshed the Columns and Data tabs. The Columns tab now shows the recreated table's new column order, but the data tab shows the columns in the order from the previous incarnation of the table.

I can recreate that, and it seems to be down to SQL Developer caching the query it's using to retrieve the data. You can recreate the table with completely different column names and refresh the Data tab, and it will still show the original column headings.

Since refreshing in that tab only refreshes the data and not the metadata, you need to either:

  • hit F5 while the Data tab is active; or
  • right-click the Tables list in the Connections window and choose refresh from there; or
  • close the MPDAT_ALT table window and reopen it from the Tables list.

The Columns and Data tabs will then both show the columns in the order you expect, as defined in the data dictionary.

All of those work in SQL Developer 4.1.3. It's possible that earlier versions might still show the old order, in which case you may have to quit SQL Developer and relaunch it to see the changes.

If you have changed the column ordering or widths in the Data tab (or persisted a find/highlight) prior to dropping/recreating the table the SQL Developer does retain the old column order in the Data tab. As you've discovered, you can right-click any column heading and choose 'Delete Persisted Settings', which will regenerate the tab with the new column order - but obviously you then lose the customised widths, or highlights, or whatever you had set up.

Alex Poole
  • 161,851
  • 8
  • 150
  • 257
  • I am using the SQL Developer 4.1.2, but it seriously doesn't change the order even after closing and refreshing everything. – Ben C. Mar 31 '16 at 15:57
  • 1
    Ah i found the solution! I can right click on a column and click Delete Persisted Settings. That solves it. Thanks a lot, I know that this was an annoying question. – Ben C. Mar 31 '16 at 16:00
  • @Puddle - I can't make [settings persist](http://www.thatjeffsmith.com/archive/2013/10/sql-developer-quick-tip-reordering-columns/) across the table being dropped and recreated, so that still doesn't really make sense. Unless you reordered the columns *after* the drop/recreate. Oh well... Ah, OK, with some data and a persisted highlight, yes, it does then keep the old order. Interesting. Or changing column width/order, but only if there is data , which I wasn't doing before. OK, interesting. – Alex Poole Mar 31 '16 at 16:21