1 Reply Latest reply on Jul 28, 2013 5:02 PM by rp0428

    Sqldeveloper 4.0EA1 can not edit 12c "invisible columns"

    uw*404948*on

      If a table contains an invisible column (which is new a new feature in 12c) this column is included in the export/generated sql as invisible, which works as expected.

       

      In dialog "edit table" it not possible to edit this column because it is not included in the column list.

       

      If would be useful if there is a checkbox to  enable/disable the visiblitity of "invisible columns" in the column list of the "edit table" dialog.

      If the "invisible column" is not shown in the editor, it is for example not possible to make it visible.

      The same would be fine in the data grid too, otherwise it is not possible to fill some data into an invisible column.

        • 1. Re: Sqldeveloper 4.0EA1 can not edit 12c "invisible columns"

          uw*404948*on wrote:

           

          If a table contains an invisible column (which is new a new feature in 12c) this column is included in the export/generated sql as invisible, which works as expected.

           

          In dialog "edit table" it not possible to edit this column because it is not included in the column list.

           

          If would be useful if there is a checkbox to  enable/disable the visiblitity of "invisible columns" in the column list of the "edit table" dialog.

          If the "invisible column" is not shown in the editor, it is for example not possible to make it visible.

          The same would be fine in the data grid too, otherwise it is not possible to fill some data into an invisible column.

          I believe that is a bug (all except the statement about the data grid) and is probably related (only a guess) to the method used by sql developer to extract the metadata for the table.

           

          The addition and implementation of invisible columns has thrown a few wrinkles into things. There is some very important information about this feature in the DBA doc.

           

          See 'Understand Invisible Columns'

          http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#BABEDBIJ

           

           

          You can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table. For example, the following operations do not display invisible columns in the output:

          •   SELECT * FROMstatements in SQL
          •   DESCRIBEcommands in SQL*Plus
          •   %ROWTYPEattribute declarations in PL/SQL
          •   Describes in Oracle Call Interface (OCI)

          You can use a SELECT statement to display output for an invisible column only if you explicitly specify the invisible column in the column list. Similarly, you can insert a value into an invisible column only if you explicitly specify the invisible column in the column list for the INSERT statement. If you omit the column list in the INSERT statement, then the statement can only insert values into visible columns.

          You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible. You can also alter a table to make a visible column invisible.

          You might use invisible columns if you want to make changes to a table without disrupting applications that use the table. After you add an invisible column to a table, queries and other operations that must access the invisible column must refer to the column explicitly by name. When you migrate the application to account for the invisible columns, you can make the invisible columns visible.

          Virtual columns can be invisible. Also, you can use an invisible column as a partitioning key during table creation.

          The following restrictions apply to invisible columns:

          •   The following types of tables cannot have invisible columns:
            • External tables

            •   Cluster tables
            •   Temporary tables
          •   Attributes of user-defined types cannot be invisible.

           

          There are some more 'wrinkles' in the 'Invisible Columns and Column Ordering' section that follows that first one.

           

          Invisible Columns and Column Ordering

          The database usually stores columns in the order in which they were listed in the CREATE TABLE statement. If you add a new column to a table, then the new column becomes the last column in the table's column order.

          When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table. Column ordering is important when all of the columns in a table are accessed. For example, a SELECT * FROM statement displays columns in the table's column order. Because invisible columns are not included in this type of generic access of a table, they are not included in the column order.

          When you make an invisible column visible, the column is included in the table's column order as the last column. When you make a visible column invisible, the invisible column is not included in the column order, and the order of the visible columns in the table might be re-arranged.

           

          That 'column ordering' part will probably have some impact on that 'data grid' issue you mentioned. A normal query won't select the invisible columns so there will NOT be a column in the data grid to make visible. So depending on the logic and query used by sql developer to populate the data grid there may not be an easy way to toggle the display of that data.

           

          It raises a number of questions for the dev team:

          1. If a column is 'invisible' should a user be able to see the data in the data grid?

          2. Should the data grid display an 'empty' column so the user can 'toggle' invisibility on/off? That means pull the data but only display it based on the toggle setting.

          3. Does the display depend on privileges on that column?

          4. How should data from a 'SELECT * query' (that does NOT mention the invisible column by name) be displayed?