0 Replies Latest reply on Aug 26, 2009 4:45 PM by 719403

    Sql Developer 1.5.5 bug with large sequence start values.

    719403
      We have a small table in our sql server 2005 schema (~700) rows. Its primary key is a bigint. We randomly generate ids for this table (don't ask). We have ids for this table which are larger than 2147483647. (java.lang.Integer.MAX_VALUE). I'm using a Windows 10g instance as the repository:

      SQL> select * from v$version
      2 ;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      CORE 10.2.0.1.0 Production
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production

      Capturing this model works fine.

      Converting this model works fine.

      When the converted model is "Generated", Sql Developer stops generating the script before the worksheet window is opened with the generated sql. If run from sqldeveloper/bin, this exception is output to the console:

      java.lang.Exception: java.util.NoSuchElementException
      at oracle.dbtools.migration.workbench.core.ui.AbstractMigrationProgressRunnable.start(AbstractMigrationProgressRunnable.java:141)
      at oracle.dbtools.migration.workbench.core.GenerateInitiator.launch(GenerateInitiator.java:42)
      at oracle.dbtools.raptor.controls.sqldialog.ObjectActionController.handleEvent(ObjectActionController.java:149)
      at oracle.ide.controller.IdeAction.performAction(IdeAction.java:524)
      at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:855)
      at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:496)
      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
      at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
      at javax.swing.AbstractButton.doClick(AbstractButton.java:357)
      at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1225)
      at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1266)
      at java.awt.Component.processMouseEvent(Component.java:6134)
      at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
      at java.awt.Component.processEvent(Component.java:5899)
      at java.awt.Container.processEvent(Container.java:2023)
      at java.awt.Component.dispatchEventImpl(Component.java:4501)
      at java.awt.Container.dispatchEventImpl(Container.java:2081)
      at java.awt.Component.dispatchEvent(Component.java:4331)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4301)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3965)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3895)
      at java.awt.Container.dispatchEventImpl(Container.java:2067)
      at java.awt.Window.dispatchEventImpl(Window.java:2458)
      at java.awt.Component.dispatchEvent(Component.java:4331)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
      Caused by: java.util.NoSuchElementException
      at oracle.dbtools.metadata.persistence.PersistableObjectIterator.next(PersistableObjectIterator.java:131)
      at oracle.dbtools.migration.generate.GenerateWorker.generateSequences(GenerateWorker.java:862)
      at oracle.dbtools.migration.generate.GenerateWorker.generateIndividualSchema(GenerateWorker.java:456)
      at oracle.dbtools.migration.generate.GenerateWorker.generateSchema(GenerateWorker.java:365)
      at oracle.dbtools.migration.generate.GenerateWorker.runGenerate(GenerateWorker.java:220)
      at oracle.dbtools.migration.workbench.core.ui.GenerateRunner.doWork(GenerateRunner.java:71)
      at oracle.dbtools.migration.workbench.core.ui.AbstractMigrationProgressRunnable.run(AbstractMigrationProgressRunnable.java:161)
      at oracle.dbtools.migration.workbench.core.ui.MigrationProgressBar.run(MigrationProgressBar.java:569)
      at java.lang.Thread.run(Thread.java:619)

      A problem row can be viewed by issuing this query against the repository:
      select * from md_sequences where seq_start > 2147483647;

      We have one row returned. The oracle.dbtools.metadata.persistence.MdSequence class uses a java.lang.Integer value to store the start value for the sequence. The value stored in the repository's MD_SEQUENCE table overflows the Integer type.

      Updating the SEQ_START column in the MD_SEQUENCE table fixed our problem. (Well, I can now generate the schema. I may have other problems with the sequence being off).

      I've written this post to save someone else the day it took me to diagnose this problem with Sql Developer 1.5.5. At the very least Sql Developer should inform the user that it could not generate the script because of the large sequence start value. It basically swallows any error and terminates script generation without any type of feedback.