6 Replies Latest reply on Mar 31, 2016 9:41 PM by rp0428

    SQL*dev object view'er shows view-columns in desc (column_id) order on some databases

    KarstenH-dk

      When Inspecting views in the object-viewer on some databases sql*dev shows views in desc (column_id) order

      as shown.

      This issue does not show on all databases - although they have the same version 12.1.0.2.0

      Tables always show in asc column_id order, but indexes have the same desc order ?

       

      viewf.jpg

      Off course there is a work-around, but it's still annoying because our users (who are not developers) are presented for the

      view in a desc order

       

      regards Karsten

        • 1. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases
          thatJeffSmith-Oracle

          That's how it's stored in the database - I see what you see for the HR view - the data dictionary is returning the column in reverse order

           

          12102_views.png

          • 2. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases
            KarstenH-dk

            The order of the query you are issuing here will be random, as no order by is given.

             

            But this is not what people expects.

             

            If you in Sql*dev -> the connection tab is selecting a view and expands the view the order to show its columns these are ordered by column_id asc.

            ( when grapping the sql from the statements tab you will find this query:


            SELECT COLUMN_NAME

                  FROM SYS.Dba_TAB_COLUMNS

                  WHERE OWNER = :SCHEMA

                  AND TABLE_NAME = :PARENT_NAME

                  ORDER BY COLUMN_ID

             

            this is for some reason NOT the case, when you look at the view in the objectviewer - when grapping the sql issued on this occasion, there's no order by given.

            on the other hand when you look at a table in this way you will find an order by column_id.

            Therefore in my opinion there's a bug here concerning views (and indexes as well)

             

            regards Karsten

            • 3. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases

              The order of the query you are issuing here will be random, as no order by is given.

              Correct - that is EXACTLY how Oracle works. No order by - no order is guaranteed. You want a specific order then use an order by. And for text you may also need to be sure the language parameters are set properly.

               

              I get DIFFERENT results that you and the other responder for that HR view. Mine shows 15 and 16 first and then 1 thru 14.

               

              Although I understand the point you are making I fail to see any real issue.

              But this is not what people expects.

              I don't understand - what is it people 'expect'?

               

              In 25+ years working with Oracle I have NEVER come across anyone that 'expected' metadata to be in order by column_id. Column id is used INTERNALLY by Oracle.

               

              A user NEVER refers to column id in queries or any other valid use of Oracle data or metadata. It is totally irrevelant to users what column id value any particular column has.

               

              The column id may even change from time to time. Create a new functional index and Oracle will 'silently' add new hidden columns to the base table. Make a column invisible and the column id may change.

               

              Users do NOT use column id and should not care what the value is today or if the value is different tomorrow.

               

              I understand that may be hard to grasp at first

              Therefore in my opinion there's a bug here concerning views (and indexes as well)

              Why? I don't see any bug. Users don't use column_id. The actual value has NO MEANING to users but only to Oracle.

               

              So I fail to understand ANY useful benefit to having columns presented in column id order when that order has NO MEANING to the users at all.

               

              What difference does it make at all if a column (e.g. salary) is listed first, last or anywhere else in between?

               

              The best 'solution' IMHO is to adjust your users expectations. If you have a user that is concerned about column_id then that user may be wandering down a 'bunny trail'.

              • 4. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases
                KarstenH-dk

                I totally disagree.

                 

                When people take a look at their tables, views and other database objects, they expect to see the same

                thing from time to time, they DO NOT expect to see columns ordered randomly.

                 

                People may not be aware of how or by what the columns are ordered, but they will expect to se

                the same order of the columns every time they describe a table (or a view etc.)

                or take a look at the object in SQL*developer or some other tool.

                 

                If columns are expected to be ordered randomly then why does the describe some_view in

                SQL*developer order by column_id ? ( grap the sql from the statements tab).

                 

                Every other tool is ordering the columns by column_id, when showing database objects,

                sqlplus, sqlcl, toad and most of SQL*developer, except for the object-viewer.

                 

                Some scripts depends on the order of the columns are "as expected".

                Although it is not recommended to omit the columns list

                it is possible to insert without specifying a list of column names thereby depending on the

                expected order of the columns ( ie. ordered by column_id)

                 

                  CREATE TABLE KHO_SLETXX

                   (NO1 NUMBER,

                   VNO2 VARCHAR2(20 BYTE),

                   DATO DATE

                   ) ;

                  

                INSERT INTO KHO_SLETXX   VALUES  (1, 'A1234', sysdate);  will insert 

                 

                INSERT INTO KHO_SLETXX   VALUES  ('A1234', sysdate,1);  fails with inconsistent datatypes

                • 5. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases
                  thatJeffSmith-Oracle

                  we should probably have an order by in there for that query, what we're showing isn't wrong, but it could be more user friendly

                   

                  please file a SR with My Oracle Support

                  • 6. Re: SQL*dev object view'er shows view-columns in desc (column_id) order on some databases

                    I totally disagree.

                    Fine by me - different people have different opinions.

                    When people take a look at their tables, views and other database objects, they expect to see the same

                    thing from time to time, they DO NOT expect to see columns ordered randomly.

                    They are free to order that 'data' anyway they want by clicking on the column header. COLUMN_ID is used for internal purposes. Any other use is frowned on and subject to the risks I mentioned and other risks.

                     

                    Personally I've never known ANYONE to say 'gee - I really need to take another look at column 11 today'. I've had MANY people say things like 'gee - I think we may need to lengthen the LAST_NAME column to accomodate the data we plan to migrate'. They really don't care if LAST_NAME is column 1, 4, 83 or anything else. And if they have a large number of columns they generally sort them by column_name so they can find them alphabetically like a phone book.

                     

                    When phone books were popular I don't recall anyone saying look me up, I'm in the book on page 273 left column line 32.

                     

                    Some scripts depends on the order of the columns are "as expected".

                    And those scripts are poorly written and the developer should be either retrained or fired.

                     

                    There is no excuse for accepting poorly written scripts or code. They should use BEST PRACTICES and that means EXPLICITLY specifying column lists in almost all cases.

                     

                    it is possible to insert without specifying a list of column names thereby depending on the

                    expected order of the columns ( ie. ordered by column_id)

                     

                      CREATE TABLE KHO_SLETXX

                       (NO1 NUMBER,

                       VNO2 VARCHAR2(20 BYTE),

                       DATO DATE

                       ) ;

                     

                    INSERT INTO KHO_SLETXX   VALUES  (1, 'A1234', sysdate);  will insert

                     

                    INSERT INTO KHO_SLETXX   VALUES  ('A1234', sysdate,1);  fails with inconsistent datatypes

                    It is possible to poke yourself in the eye with a stick also but I don't recommend it.

                     

                    Those two inserts should be REJECTED during any code review. You, the user, do NOT have control over the order of the columns even if you do create the table.

                     

                    If an application update adds new columns to that table those inserts will fail because they only provide 3 values but the table now has more that three columns.

                     

                    The simple act of making a column invisible and then visible again will change the column_id value and make those inserts fail.

                     

                    You should NEVER rely on any apparent physical order to columns. You and others are entitled to your own opinions. As for me I teach and train what I said above and I enforce that standard during code reviews. Any developer working for me will do it that way or will need to relocate.

                     

                    There are valid reasons for doing it that way and I still haven't heard one valid reason (IMHO) for not doing it that way.

                     

                    As for Sql Dev - it is their tool so they can order the data anyway they want and it won't affect me or my developers one bit. It certainly isn't something that I would spend any of MY money doing when there is more important work to be done.