Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

Sql Dev hangs when altering a procedure with wrong name

5a73e128-a4bb-4ddb-9c09-78a783c9abb7
edited Feb 29, 2020 11:54AM in SQL Developer

Is it an old bug?

I have a procedure to do enhancement. Sometimes i clone it in another name for coding / testing and then copy the code back.

If i forgot to change back the procedure name and commit, SQL Developer hangs and never resumes. I can only kill it and the unsaved tabs are totally gone.

Is there workaround to avoid this situation or at least to get back the unsaved tabs?

Thanks.

Tagged:
B.DelméethatJeffSmith-Oracle

Answers

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 27, 2020 4:38AM

    Unfortunately it has been like this forever in sqldeveloper (one sure way to get stuck!).

    What i try to do (when i do not forget) is Ctrl-A, copy all code from the procedure editor into a new worksheet, alter the procedure name in said worksheet then "run as script" (F5).

    Then open the new procedure from the object browser in the PL/SQL editor to start altering it.

    Same behaviour for packages/procedures/functions.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,088 Employee
    edited Feb 27, 2020 6:41AM

    Can you share a list of steps to make this happen?

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 27, 2020 8:42AM

    Hmm ... i just tried to find back an old post of mine where i would have asked about this before, but could not find one...

    Maybe because i was never sure to have a 100% reproducible scenario...

    Anyway, here's what worked for me twice in a row with sqldev 19.4 (win 7, JDK1.8.0_192, 32bit)

    create a new function, make it so:

    CREATE OR REPLACE FUNCTION FUNCTION1 RETURN VARCHAR2 AS BEGIN  RETURN NULL;END FUNCTION1;

    compile it by using Ctrl-S (*not* the compile button)

    do not close editor, just alter content so it looks as follows:

    CREATE OR REPLACE FUNCTION FUNCTION2 RETURN VARCHAR2 AS BEGIN  RETURN 'DoH!';END FUNCTION2;

    hit Ctrl-S - you should be stuck (at least I was twice today)

    Consider this is actually a fairly common scenario:

    you have a piece of PL/SQL code that takes a long time to complete (or gets invoked often from a scheduler)

    If you need to improve your code without suffering a long timeout when you try to compile because the code is locked running within the database, you can simply work on a copy of the original code until you're happy with the altered copy to replace the original. I got stuck often times at this "copy" step (performed by renaming the original in the editor then compiling).

    I got in the habit of compiling by hitting Ctrl,-S and at least today with this current version of sql dev that seemed to create the problem whereas using the button does not. YMMV.

    When this happens, sqldeveloper does not seem (according to the task manager) to consume any CPU; it seems to me more like it is waiting for someone to confirm some invisible dialog or something.

    When I kill and restart a fresh instance, the renamed function copy has actually been created and can be worked on.

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Feb 27, 2020 4:25PM

    The jstack full thread dump for your scenario (using SQL Dev 19.4 with jdk1.8.0_221) contains a deadlock report

    Found one Java-level deadlock:============================="AsynchronousIdeActionHandler_TI":  waiting to lock monitor 0x00000000268f4668 (object 0x0000000080002300, a java.awt.Component$AWTTreeLock),  which is held by "AWT-EventQueue-0""AWT-EventQueue-0":  waiting for ownable synchronizer 0x00000000e8c29b70, (a java.util.concurrent.locks.ReentrantReadWriteLock$NonfairSync),  which is held by "AsynchronousIdeActionHandler_TI"

    with the following details on those two threads, perhaps indicating some conflict between closing editor panes and updating the connections navigator tree?

    Java stack information for the threads listed above:==================================================="AsynchronousIdeActionHandler_TI":    at java.awt.Container.remove(Container.java:1263)    - waiting to lock <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JViewport.remove(JViewport.java:359)    at javax.swing.JViewport.setView(JViewport.java:956)    at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.detachCurrentEditor(SplitPane.java:1030)    at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.setSplitPaneState(SplitPane.java:274)    at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroupState.closeSplitPaneState(TabGroupState.java:598)    at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.closeEditors(TabGroup.java:630)    at com.oracle.jdeveloper.nbwindowsystem.NbEditorContainer.closeEditors(NbEditorContainer.java:343)    at com.oracle.jdeveloper.nbwindowsystem.NbEditorContainer.componentClosed(NbEditorContainer.java:272)    at org.openide.windows.WindowManager.componentCloseNotify(WindowManager.java:322)    at org.netbeans.core.windows.WindowManagerImpl.notifyTopComponentClosed(WindowManagerImpl.java:1156)    at org.netbeans.core.windows.Central.removeModeTopComponent(Central.java:1025)    at org.netbeans.core.windows.ModeImpl.removeTopComponent(ModeImpl.java:478)    at org.netbeans.core.windows.ModeImpl.close(ModeImpl.java:286)    at org.netbeans.core.windows.WindowManagerImpl.topComponentClose(WindowManagerImpl.java:1321)    at org.openide.windows.TopComponent.close(TopComponent.java:561)    at org.openide.windows.TopComponent.close(TopComponent.java:548)    at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.closeEditors(NbEditorManager.java:966)    at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.closeEditors(NbEditorManager.java:822)    at oracle.dbtools.raptor.navigator.plsql.PlSqlNode$3.run(PlSqlNode.java:498)    at oracle.dbtools.raptor.navigator.plsql.PlSqlNode.saveImpl(PlSqlNode.java:503)    at oracle.ide.model.Node.save(Node.java:1402)    at oracle.ide.cmd.SaveCommand._saveNode(SaveCommand.java:176)    at oracle.ide.cmd.SaveCommand.doit(SaveCommand.java:66)    at oracle.ide.controller.CommandProcessor.invoke(CommandProcessor.java:377)    at oracle.ide.IdeController.handleSaveCommand(IdeController.java:420)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:498)    at oracle.ide.controller.AnnotatedController$CmdInfo.invoke(AnnotatedController.java:255)    at oracle.ide.controller.AnnotatedController$CmdInfo.handle(AnnotatedController.java:241)    at oracle.ide.controller.AnnotatedController.handleEvent(AnnotatedController.java:72)    at oracle.ide.controller.AsynchronousController.handleEvent(AsynchronousController.java:161)    at oracle.ide.controller.AsynchronousController$AcRunnable.run(AsynchronousController.java:555)    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443)    at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68)    at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303)    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058)"AWT-EventQueue-0":    at sun.misc.Unsafe.park(Native Method)    - parking to wait for  <0x00000000e8c29b70> (a java.util.concurrent.locks.ReentrantReadWriteLock$NonfairSync)    at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)    at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:836)    at java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireShared(AbstractQueuedSynchronizer.java:967)    at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireShared(AbstractQueuedSynchronizer.java:1283)    at java.util.concurrent.locks.ReentrantReadWriteLock$ReadLock.lock(ReentrantReadWriteLock.java:727)    at oracle.javatools.buffer.JdkReadWriteLockImplementation.lock(JdkReadWriteLockImplementation.java:202)    at oracle.javatools.buffer.JdkReadWriteLockImplementation.readLock(JdkReadWriteLockImplementation.java:94)    at oracle.javatools.buffer.ReadWriteLock.readLock(ReadWriteLock.java:188)    at oracle.javatools.buffer.AbstractTextBuffer.readLock(AbstractTextBuffer.java:944)    at oracle.javatools.buffer.ReadTextBufferDecorator.readLock(ReadTextBufferDecorator.java:126)    at oracle.javatools.editor.BasicDocument.readLock(BasicDocument.java:436)    at oracle.javatools.editor.folding.DefaultCodeFoldingModel.readLock(DefaultCodeFoldingModel.java:198)    at oracle.javatools.editor.folding.CodeFoldingMargin.paintComponent(CodeFoldingMargin.java:855)    at javax.swing.JComponent.paint(JComponent.java:1056)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JViewport.paint(JViewport.java:728)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JLayer.paint(JLayer.java:433)    at javax.swing.plaf.LayerUI.paint(LayerUI.java:79)    at javax.swing.plaf.ComponentUI.update(ComponentUI.java:161)    at javax.swing.JComponent.paintComponent(JComponent.java:780)    at javax.swing.JLayer.paint(JLayer.java:428)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at org.netbeans.swing.tabcontrol.TabbedContainer.paint(TabbedContainer.java:994)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JLayeredPane.paint(JLayeredPane.java:586)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at org.netbeans.core.windows.view.ui.MainWindow$1.paint(MainWindow.java:189)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JLayeredPane.paint(JLayeredPane.java:586)    at javax.swing.JComponent.paintChildren(JComponent.java:889)    - locked <0x0000000080002300> (a java.awt.Component$AWTTreeLock)    at javax.swing.JComponent.paint(JComponent.java:1065)    at javax.swing.JComponent.paintToOffscreen(JComponent.java:5210)    at javax.swing.RepaintManager$PaintManager.paintDoubleBuffered(RepaintManager.java:1579)    at javax.swing.RepaintManager$PaintManager.paint(RepaintManager.java:1502)    at javax.swing.RepaintManager.paint(RepaintManager.java:1272)    at javax.swing.JComponent._paintImmediately(JComponent.java:5158)    at javax.swing.JComponent.paintImmediately(JComponent.java:4969)    at javax.swing.RepaintManager$4.run(RepaintManager.java:831)    at javax.swing.RepaintManager$4.run(RepaintManager.java:814)    at java.security.AccessController.doPrivileged(Native Method)    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)    at javax.swing.RepaintManager.paintDirtyRegions(RepaintManager.java:814)    at javax.swing.RepaintManager.paintDirtyRegions(RepaintManager.java:789)    at javax.swing.RepaintManager.prePaintDirtyRegions(RepaintManager.java:738)    at javax.swing.RepaintManager.access$1200(RepaintManager.java:64)    at javax.swing.RepaintManager$ProcessingRunnable.run(RepaintManager.java:1732)    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)    at java.awt.EventQueue.access$500(EventQueue.java:97)    at java.awt.EventQueue$3.run(EventQueue.java:709)    at java.awt.EventQueue$3.run(EventQueue.java:703)    at java.security.AccessController.doPrivileged(Native Method)    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)    at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)    at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)Found 1 deadlock.                 
    B.Delmée
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,088 Employee
    edited Feb 27, 2020 7:58PM

    With the stack trace, the developer was able to see the issue right away, nice work folks!

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 28, 2020 6:08AM

    Thanks Glen, glad someone could confirm (and provide the extra details).

    Now i am curious: does it happen on all supported platforms and JDK versions?

    Does it really only happen when using the keyboard shortcut ?

    Confirming using the "compile" button instead is a work-around would be useful as not everyone may be able or want to upgrade to the latest and greatest.

    Jeff, i imagine it is too late for a fix to make it to 20.1 ?

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Feb 28, 2020 11:07AM

    Well, based on Jeff's comment of

    With the stack trace, the developer was able to see the issue right away

    I am led to believe the deadlock is the result of a Java coding issue and likely applies for all platforms and JDK versions.

    The hang occurs immediately when using the Ctrl-S technique or File > Save, but the Compile options on the Code Editor gear widget work fine.  You will also notice that Compile produces an informational dialog

    Capture.JPG

    which the other code paths miss entirely.  I personally tested only on WIndows 10 with jdk1.8.0_221 and jdk-13.0.1, and I guess that is sufficient for a sanity check.

    Finally, I have always used the Compile options -- never even occurred to me to try Save.

    Cheers

    thatJeffSmith-OraclethatJeffSmith-Oracle
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,088 Employee
    edited Feb 28, 2020 2:13PM

    >>Jeff, i imagine it is too late for a fix to make it to 20.1 ?

    Nope, not too late

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 29, 2020 11:54AM

    Tx again for checking into this, Glen. Now i just need to learn to click more ,-)

    It would be interesting if the OP could confirm she/he was using Ctrl-S, too.

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Feb 29, 2020 11:47AM

    Excellent, tx for listening and fixing our issues.