4 Replies Latest reply on Sep 7, 2016 1:23 PM by Raj Jamadagni

    Bulk Reordering of column_ids, not supported.

    3272717

      For reasons unexplained I am trying to sync up columns_id between two tables with the same columns in both, but in different ordering.

      Column_Name   Column_ID

      COL1                   1

      COL2                   2

      COL7                   3

      COL4                   4

      COL5                   5

      COL8                   6

      COL3                   7

      COL6                   8

       

      In this case the mismatch begins with column 3 this GLDS table:

       

      I turn the columns invisible:

      alter table test_GLDS modify( COL3 invisible, COL4 invisible, COL5 invisible, COL6 invisible, COL7 invisible, COL8 invisible);

       

       

      I check to see if the column_id are null:

      select COLUMN_NAME,COLUMN_ID from dba_tab_columns where owner ='ADMIN' AND TABLE_NAME = UPPER('test_gldS') order by column_id;

       

      Turn the columns visible in subsequent order to

      alter table test_GLDS modify( COL3 visible);

      alter table test_GLDS modify( COL4 visible);

      alter table test_GLDS modify( COL5 visible);

      alter table test_GLDS modify( COL6 visible);

      alter table test_GLDS modify( COL7 visible);

      alter table test_GLDS modify( COL8 visible);

       

      Column_Name   Column_ID

      COL1                        1

      COL2                        2

      COL3                        3

      COL4                        4

      COL5                        5

      COL6                        6

      COL7                        7

      COL8                        8

       

       

      Just why doesn't this following command do the same thing?   I even precede it with a commit;

       

      alter table test_gldS modify( COL3 visible, COL4 visible, COL5 visible, COL6 visible, COL7 visible, COL8 visible);

      commit;

       

      There should be a DBMS method to reorder the columns.

        • 1. Re: Bulk Reordering of column_ids, not supported.

          First - your question has NOTHING to do with Sql Developer which is the topic for this forum. Please mark the thread ANSWERED and repost in the General DB forum.

          General Database Discussions

          For reasons unexplained I am trying to sync up columns_id between two tables with the same columns in both, but in different ordering.

          The reasons are 'unexplained' because you haven't explained them.

           

          There should be a DBMS method to reorder the columns.

          No - there should NOT be such a method.

           

          In database work the order of the columns in a table has no meaning/significance at all. It is totally irrevelant.

           

          And with Oracle it is NOT possible to try to control it since there are MANY things that cause Oracle to create new columns that you likely aren't even aware of.

           

          Create an object type with 5 attributes and use it as a column: Oracle will create SIX new columns. One of them is visible but the other 5 (that match the 5 attributes) will be hidden.

           

          That ordering you are trying to do using visible/invisible does NOT change the physical order of the columns. It ONLY changes the logical Oracle.

           

          Your code/apps have a MAJOR flaw if they try to depend on the physical order of columns.

           

          And if you want a specific logical order just create a view and order the projected columns any way you wish.

           

          If you need more help than the above please repost in the proper forum.

          • 2. Re: Bulk Reordering of column_ids, not supported.
            Raj Jamadagni

            Perhaps Database Ideas is a forum OP should try.

            • 3. Re: Bulk Reordering of column_ids, not supported.
              Sven W.

              Raj Jamadagni wrote:

               

              Perhaps Database Ideas is a forum OP should try.

              Absolutely not! The ideas forum is not ment to suggest very strange once in a time requirements.

              • 4. Re: Bulk Reordering of column_ids, not supported.
                Raj Jamadagni

                Well, i think think that is the correct forum, which part of 'ideas' grants one to be the gatekeeper?

                I say let OP post andd let general population decide rather than one person, you are welcome to post your opinion about this idea in that forum not here.