14 Replies Latest reply on Feb 15, 2018 10:54 PM by Gary Graham-Oracle

    SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?

    Joe G.

      I have been encountering an issue with SQL Developer for several years and it has persisted from previous versions until the current version.

       

      I use the "Edit Table" screen (by right-clicking on a table and clicking Edit) to create and edit my tables. Sometimes I will add an Identity Column of "Type: Column Sequence" to my table (which auto-creates the trigger and sequence to enable auto inserting of consecutive numbers in my table's primary key). But it seems that every so often when I make a small change to a table (a change like deleting an unused column) it resets the Identity Column on my primary key back to "Type: None". I usually only discover this when an insert to that table fails because there is no number inserted into the primary key (because the Identity Column quit working). That's when I go into SQL Developer / Edit table and discover the Identity Column on my primary key has been set back to "Type: None". There is no person doing this, as I am the only one working on these tables.

       

      I cannot figure out if this is a bug, or something about the functionality that I am missing?

       

      It causes me lots of issues when it keeps causing my applications to throw errors. So I would love to figure this out.

       

      Can anyone help?

       

      Thanks!

        • 1. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
          thatJeffSmith-Oracle

          Version?

           

          I logged a pretty bad bug where togging around the create/edit Table dialog was resetting form properties back to defaults, like your column name would go back to 'column16' if you clicked in and out of the columns page.

          • 2. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
            Joe G.

            Version 17.4.0.355

            Build 355.2349

             

            Hopefully that is the latest. I try to keep it up to date in the hopes that the problem was fixed. :-)

             

            Thanks for your help.

            • 3. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
              Paul Simmons

              If the identify column is never to be published to users you could start defining it as a raw(32) and a default of sys_guid().  crude tests I have performed equate to roughly the same performance as an uncached sequence and a trigger.

               

              SQL> 
              SQL> CREATE TABLE test1 (
                2  ID RAW(32) DEFAULT sys_guid()
                3  , n number);
              
              
              Table TEST1 created.
              
              
              Elapsed: 00:00:00.019
              SQL> 
              SQL> CREATE TABLE test2 (
                2  ID NUMBER
                3  ,N NUMBER);
              
              
              Table TEST2 created.
              
              
              Elapsed: 00:00:00.015
              SQL> 
              SQL> CREATE SEQUENCE sequence2
                2  START WITH 1
                3  INCREMENT BY 1;
              
              
              Sequence SEQUENCE2 created.
              
              
              Elapsed: 00:00:00.005
              SQL> 
              SQL> CREATE TRIGGER test2_BI
                2  BEFORE INSERT
                3  ON test2
                4  for each row
                5  BEGIN
                6    :new.id := sequence2.nextval;
                7  END;
                8  /
              
              
              Trigger TEST2_BI compiled
              
              
              Elapsed: 00:00:00.061
              SQL> 
              SQL> 
              SQL> BEGIN
                2    -- testing default on sys_guid
                3    FOR x IN 1..10
                4    loop
                5      FOR I IN 1..10000
                6      LOOP
                7        insert into test1 (n) values (ora_hash(systimestamp));
                8      END LOOP;
                9      COMMIT;
               10    END LOOP;
               11    commit;
               12  end;
               13  /
              
              
              
              

               

              SQL> BEGIN
                2    -- testing default on sys_guid
                3    FOR x IN 1..10
                4    loop
                5      FOR I IN 1..10000
                6      LOOP
                7        insert into test1 (n) values (ora_hash(systimestamp));
                8      END LOOP;
                9      COMMIT;
               10    END LOOP;
               11    commit;
               12  end;
               13  /
              
              
              PL/SQL procedure successfully completed.
              
              
              Elapsed: 00:00:09.828
              

               

              SQL> BEGIN
                2    -- testing default on sys_guid
                3    FOR x IN 1..10
                4    loop
                5      FOR I IN 1..10000
                6      LOOP
                7        insert into test1 (n) values (ora_hash(systimestamp));
                8      END LOOP;
                9      COMMIT;
               10    END LOOP;
               11    commit;
               12  end;
               13  /
              
              
              PL/SQL procedure successfully completed.
              
              
              Elapsed: 00:00:10.032
              

               

              SQL> BEGIN
                2    -- testing default on sequence
                3    FOR x IN 1..10
                4    loop
                5      FOR I IN 1..10000
                6      LOOP
                7        insert into test2 (n) values (ora_hash(systimestamp));
                8      END LOOP;
                9      COMMIT;
               10    END LOOP;
               11    commit;
               12  END;
               13  /
              
              
              PL/SQL procedure successfully completed.
              
              
              Elapsed: 00:00:16.103
              

               

               

              SQL> BEGIN
                2    -- testing default on sequence
                3    FOR x IN 1..10
                4    loop
                5      FOR I IN 1..10000
                6      LOOP
                7        insert into test2 (n) values (ora_hash(systimestamp));
                8      END LOOP;
                9      COMMIT;
               10    END LOOP;
               11    commit;
               12  END;
               13  /
              
              
              PL/SQL procedure successfully completed.
              
              
              Elapsed: 00:00:09.455
              

               

               

              My personal preference is for sys_guid() as you never have to worry about 2 additional database objects (sequence and trigger) and it is safe to be unique across all databases.

              1 person found this helpful
              • 4. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                Joe G.

                Thank you Paul for the idea. I never realized (or thought of) using a GUID for a primary key. It is a good thing to know.

                 

                My only concern is that sometimes I have thousands of records flowing across our network, and I would be a bit concerned how much those extra 12 bytes per record would equate to a slower performance.

                 

                But who knows, maybe the difference would be negligible...at least compared to the problems from a failed insert (with my current method that keeps failing)!

                 

                Joe

                • 5. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                  thatJeffSmith-Oracle

                  GUID's for PK's is a bit of a religious war...there are strong opinions on both sides.

                  1 person found this helpful
                  • 6. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                    Paul Simmons

                    I am sure that it is.  I would only use GUID if it was only ever to be used internally in the system, or if I had two way replication and needed to guarantee primary key uniqueness.  There is still a time and place for sequences

                    • 7. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                      Joe G.

                      Hmmm...no answer yet...so please let me ask the following...

                       

                      PLEASE, for everyone who reads this who uses the "Edit Table" function within SQL Developer to add Identity Columns (Column Sequences) please post your answer to this question...

                       

                      Have you ever experienced what I have described above (the Identity Column on your primary key being set back to "Type: None" although no person is changing it)?

                       

                      I ask because it happens to me every single day that I do much table editing. Knowing if it is a unique problem to me, or not, would really shed some light on how to fix it.

                       

                      Thanks!

                       

                      Joe

                      • 8. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                        Paul Simmons

                        While the GUI is a convenient method to create and build the object, I would encourage you to learn the SQL code underneath that it executes.

                        • 9. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                          Joe G.

                          Paul,

                           

                          Perhaps your suggestion uncovers yet another religious war... (Okay, maybe not quite. )

                           

                          May I ask you a question: have you learned the C++ code that executes underneath the SQL code that you execute?

                           

                          To me, using the built in editor is a time saving tool, as so many other tools we all use in everyday life. And while I understand many complex things in my life, I cannot learn everything just for the sake of learning...there just isn't enough time. That is, unless I am convinced there is some added benefit to learning it.

                           

                          Thank you,

                           

                          Joe

                          • 10. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                            thatJeffSmith-Oracle

                            I've tried twice, once using 18.1...what we're working on now to release later this quarter, and once using 17.4.

                             

                            What I have: a predefined table with normal columns.

                             

                            Right-click > EDIT.

                             

                            Add a new column. Data type INT. Set up as IDENTITY. Start with 1, increment 1.

                             

                            Now click around and change up other columns..increase size of a varchar2. Change default for a date to SYSDATE.

                             

                            I then go and add an additional column.

                             

                            Now I go back to inspect the identity column. It's still intact. And the generated DDL is also good.

                             

                            You're going to need an exact, reproducible scenario for anyone here to take a look. And if you come up with one, that would be a bug. And that needs to get reported to My Oracle Support.

                            • 11. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                              Joe G.

                              Thanks for trying Jeff.

                               

                              I have tried to determine a reproducible sequence of events, but have not been able to do so. I realize having that would be ideal. When I have time I will try to figure it out.

                               

                              Joe

                              • 12. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                                Joe G.

                                Jeff,

                                 

                                The problem occurred today again and although I do not know all the events that led up to it, I learned a couple of things:

                                 

                                1. It seems to require the inserting of a row into the table (and then the opening of the table in the Edit function) to trigger the issue.

                                2. When it occurred, I checked the trigger that had been auto-created by the GUI, and it had been altered. It looked like this:

                                create or replace TRIGGER "PLMTEST1".LEI_EC_CHECKLISTS_TRG

                                BEFORE INSERT ON LEI_EC_CHECKLISTS

                                FOR EACH ROW

                                BEGIN

                                  <<COLUMN_SEQUENCES>>

                                  BEGIN

                                    NULL;

                                  END COLUMN_SEQUENCES;

                                END;

                                 

                                Obviously the "NULL;" line should be the code to insert the next value from the sequence. So I wonder what could have altered it?

                                 

                                Also, after reading Tom Kyte's suggestion here: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6575961912937

                                I decided I will just use a sequence alone...no trigger. So I learned how to do so using SQL (very easy) and will no longer be using the function in SQL Developer/Edit to do it for me. But I have maybe 35 other tables still using the old way, which I am in no hurry to change.

                                 

                                Thanks,

                                 

                                Joe

                                • 13. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                                  Gary Graham-Oracle

                                  it seems that every so often when I make a small change to a table (a change like deleting an unused column) it resets the Identity Column on my primary key back to "Type: None".

                                  Try this simple test case.  First the set-up steps...

                                  1. Open a connection (e.g., a Basic connection type, default JDBC thin driver, HR schema on a local 11g XE instance)

                                  2. On Tables node, right-click to bring up the context menu, then click on New Table.

                                  3. Use the default table name, say TABLE1, with the default COLUMN1 already populated.

                                  4. Click on the toolbar's Add Column icon (green plus-sign) to add COLUMN2.

                                  5. Return the focus to the COLUMN1 row, and update the Identity Column tab to set Type to Column Sequence.

                                  6. Exit the editor by clicking on OK.

                                   

                                  Now, continuing, Scenario A:

                                  7. Edit TABLE1 again.

                                  8. Immediately place the focus on COLUMN2 and delete it by clicking on the toolbar's Remove Column icon (red X).

                                  9. Exit the editor by clicking on OK.  The issue you are suffering from occurs (Identity Column tab will show Type is None).

                                   

                                  Or, Scenario B:

                                  7. Edit TABLE1 again.

                                  8. Immediately place the focus on COLUMN1, click on the Identity Column tab and see that the column sequence is still selected.

                                  9. Now place the focus on COLUMN2 and delete it by clicking on the toolbar's Remove Column icon (red X).

                                  10. Exit the editor by clicking on OK.  The issue you are suffering from does not occur.

                                   

                                  If you had multiple columns identified as column sequences in the same table, possibly the Scenario B technique would also work if you checked each such column prior to deleting a column (or whatever other change "triggers" the same behavior) but I did not test it.

                                  • 14. Re: SQL Developer / Identity Column issue - is this a bug? or am I doing something wrong?
                                    Gary Graham-Oracle

                                    A prior discussion, Issues with Create Table , resulted in a bug being logged, probably the one Jeff references in his first post above, that is still open.  Title is "FORUM: NEW TABLE DIALOG ADDING COLUMNS VIA DOWN ARROW CAN IGNORE USER COL NAME"