Search code examples
sqldatabaseoracle-databaseoracle-sqldevelopercolumnsorting

Other column order than expected

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?

Solution

  • 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.