1 2 Previous Next 16 Replies Latest reply: Feb 17, 2012 6:52 PM by Gary Graham-Oracle RSS

    3.1EA1 Can not Cancel PLSQL Block

    AM
      In 3.0EA1 hitting cancel on script output window or task progress window does not abort, just shows message "Cancel Requested". This is working in 3.0.4.34.
      This happens for PLSQL Block only. Canceling query is working fine.
      var n_count number;
      begin
      select count(1) into :n_count from large_table;
      end;
      /
      Now hit cancel. You will see message "Cancel Requested" in Task Progress but the statement continues.
      Do the same in version 3.0.4.34. It aborts with "user requested cancel of current operation"
        • 1. Re: 3.1EA1 Can not Cancel PLSQL Block
          Vadim Tropashko-Oracle
          I can't reproduce. Tested in 3.0.3.97, 3.0.4.61, 3.1.5.18:

          Edit (Opps: the latest i looked was 3.1.*6*.18)

          DECLARE
          i INTEGER;
          BEGIN
          SELECT count(1) INTO i FROM all_objects,all_all_tables;
          END;
          /

          DECLARE
          i INTEGER;
          BEGIN
          SELECT count(1) INTO i FROM dual
          CONNECT BY ROWNUM < 10000000;
          END;
          /

          ORA-01013: user requested cancel of current operation
          ORA-06512: at line 4
          01013. 00000 - "user requested cancel of current operation"

          Edited by: Vadim Tropashko on Oct 21, 2011 11:09 AM
          • 2. Re: 3.1EA1 Can not Cancel PLSQL Block
            AM
            Try on version 3.1.05.97
            I able to replicate using first example from you
            • 3. Re: 3.1EA1 Can not Cancel PLSQL Block
              -K-
              Then you should provide more details; OS/JDK/DB/Oracle client versions and connection setup (especially thin vs. thick driver).

              Regards,
              K.
              • 4. Re: 3.1EA1 Can not Cancel PLSQL Block
                AM
                Here is my environment info:

                Running on Windows XP service pack 3. Tries with both think and thick driver with same result (did this using check box in preference window)
                About

                Oracle SQL Developer 3.1.05
                Version 3.1.05
                Build MAIN-05.97
                Copyright © 2005, 2011 Oracle. All Rights Reserved.
                IDE Version: 11.1.1.4.37.59.43
                Product ID: oracle.sqldeveloper
                Product Version: 11.2.0.05.97

                Version

                Component     Version
                =========     =======
                Java(TM) Platform     1.6.0_27
                Oracle IDE     3.1.05.97
                Versioning Support     3.1.05.97

                Edited by: user4088733 on Oct 21, 2011 11:29 AM
                • 5. Re: 3.1EA1 Can not Cancel PLSQL Block
                  Vadim Tropashko-Oracle
                  RDBMS info please?
                  • 6. Re: 3.1EA1 Can not Cancel PLSQL Block
                    AM
                    Here is DB Info:

                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    PL/SQL Release 11.1.0.7.0 - Production
                    CORE     11.1.0.7.0     Production
                    TNS for Linux: Version 11.1.0.7.0 - Production
                    NLSRTL Version 11.1.0.7.0 - Production

                    Same issue on:
                    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
                    PL/SQL Release 10.2.0.5.0 - Production
                    CORE     10.2.0.5.0     Production
                    TNS for Linux: Version 10.2.0.5.0 - Production
                    NLSRTL Version 10.2.0.5.0 - Production

                    Note: I am able to cancel pl/sql block connected to same DB using Sql Developer *3.0.04.34*
                    • 7. Re: 3.1EA1 Can not Cancel PLSQL Block
                      AM
                      Is this a bug or something with my environment ?
                      • 8. Re: 3.1EA1 Can not Cancel PLSQL Block
                        Vadim Tropashko-Oracle
                        To prove it's a bug I have to reproduce it -- which I'm unable to:-(
                        • 9. Re: 3.1EA1 Can not Cancel PLSQL Block
                          AM
                          If it helps here is my console output.
                          On hitting cancel in 3EA3 I get following:

                          Exception in thread "AWT-EventQueue-0" java.lang.AbstractMethodError: oracle.jdb
                          c.driver.OracleCallableStatementWrapper.isClosed()Z
                          at oracle.dbtools.raptor.newscriptrunner.SQLCommandRunner.interrupt(SQLC
                          ommandRunner.java:68)
                          at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.cancel(ScriptRunne
                          r.java:269)
                          at oracle.dbtools.worksheet.commands.scriptrunner.task.executor.DefaultS
                          tatementExecutorTask.cancel(DefaultStatementExecutorTask.java:56)
                          at oracle.dbtools.worksheet.commands.scriptrunner.task.ScriptRunnerTask.
                          cancel(ScriptRunnerTask.java:211)
                          at oracle.dbtools.raptor.backgroundTask.RaptorTask.requestCancel(RaptorT
                          ask.java:121)
                          at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager.cancelTask(Rap
                          torTaskManager.java:383)
                          at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$DefaultTaskUIL
                          istener.cancelClicked(RaptorTaskManager.java:578)
                          at oracle.dbtools.raptor.backgroundTask.internal.SimpleRaptorTaskUI.noti
                          fyListener(SimpleRaptorTaskUI.java:137)
                          at oracle.dbtools.raptor.backgroundTask.internal.SimpleRaptorTaskUI$6.ac
                          tionPerformed(SimpleRaptorTaskUI.java:289)
                          at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
                          at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
                          at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
                          at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
                          at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
                          ce)
                          at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
                          at java.awt.Component.processMouseEvent(Unknown Source)
                          at javax.swing.JComponent.processMouseEvent(Unknown Source)
                          at java.awt.Component.processEvent(Unknown Source)
                          at java.awt.Container.processEvent(Unknown Source)
                          at java.awt.Component.dispatchEventImpl(Unknown Source)
                          at java.awt.Container.dispatchEventImpl(Unknown Source)
                          at java.awt.Component.dispatchEvent(Unknown Source)
                          at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
                          at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
                          at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
                          at java.awt.Container.dispatchEventImpl(Unknown Source)
                          at java.awt.Window.dispatchEventImpl(Unknown Source)
                          at java.awt.Component.dispatchEvent(Unknown Source)
                          at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
                          at java.awt.EventQueue.access$000(Unknown Source)
                          at java.awt.EventQueue$1.run(Unknown Source)
                          at java.awt.EventQueue$1.run(Unknown Source)
                          at java.security.AccessController.doPrivileged(Native Method)
                          at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
                          Source)
                          at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
                          Source)
                          at java.awt.EventQueue$2.run(Unknown Source)
                          at java.awt.EventQueue$2.run(Unknown Source)
                          at java.security.AccessController.doPrivileged(Native Method)
                          at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
                          Source)
                          at java.awt.EventQueue.dispatchEvent(Unknown Source)
                          at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
                          at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
                          at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
                          at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
                          at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
                          at java.awt.EventDispatchThread.run(Unknown Source)
                          • 10. Re: 3.1EA1 Can not Cancel PLSQL Block
                            Gary Graham-Oracle
                            Hi,

                            Looks like you have a JDBC version mismatch in your environment. This seems strange since you say SQL Developer 3.0 works but not 3.1 and they both require the same version. Are you running both on the same machine with the same JDK version?

                            From a web search:
                            +"With JDBC, that error usually occurs because your JDBC driver implements an older version of the JDBC API than the one included in your JRE. These older versions are fine so long as you don't try and use a method that appeared in the newer API."+

                            Regards,
                            Gary
                            SQL Developer Team
                            • 11. Re: 3.1EA1 Can not Cancel PLSQL Block
                              AM
                              Yes, both 3.0 and 3.1 are using same JDK and same machine. Also tried on 3.1EA3 with JRE included with same result.
                              Also is there a way to force SQL Developer to use different JDBC driver ?
                              Currently it picks jar from my oracle client install folder.
                              • 12. Re: 3.1EA1 Can not Cancel PLSQL Block
                                Gary Graham-Oracle
                                The classic response to that question (if you don't need to use the OCI/Thick driver, and the internal thin driver is enough) is:
                                Re: Sqldeveloper and multiple homes - how to force it to use included jdbc

                                If you do use OCI/Thick, then you will want to have some version of the 11g client installed. If multiple clients are installed, you might need to adjust their order in your PATH variable.

                                -Gary
                                • 13. Re: 3.1EA1 Can not Cancel PLSQL Block
                                  user9378013
                                  I can reproduce the pb at will with version *3.1 EA3* with the test case provided in one of the previous posts. Although cancel works properly with v3.0 (production release).

                                  Version details for 3.0:
                                  Oracle SQL Developer 3.0.04
                                  Version 3.0.04
                                  Build MAIN-04.34
                                  Copyright © 2005, 2011 Oracle. All Rights Reserved.
                                  IDE Version: 11.1.1.4.37.59.31
                                  Product ID: oracle.sqldeveloper
                                  Product Version: 11.1.2.04.34

                                  Java(TM) Platform     1.6.0_11
                                  Oracle IDE     3.0.04.34
                                  Versioning Support     3.0.04.34

                                  Version details for 3.1:
                                  Oracle SQL Developer 3.1.06
                                  Version 3.1.06
                                  Build MAIN-06.82
                                  Copyright © 2005, 2011 Oracle. All Rights Reserved.
                                  IDE Version: 11.1.1.4.37.59.48
                                  Product ID: oracle.sqldeveloper
                                  Product Version: 11.2.0.06.82

                                  Java(TM) Platform     1.6.0_11
                                  Oracle IDE     3.1.06.82
                                  Versioning Support     3.1.06.82

                                  Ah, and one detail that can have its importance: I am not using JDBC to connect to the database (11.2.0.2 Enterprise Ed.). I use an Oracle 11.2.0.1 client for windows 32 bits, on windows 7 Enterprise SP1.

                                  Regards,
                                  Olivier.
                                  • 14. Re: 3.1EA1 Can not Cancel PLSQL Block
                                    Gary Graham-Oracle
                                    Hi Olivier,

                                    Thanks for being persistent. Using Vadim's test case I was able to reproduce the behavior and logged an internal bug for it:
                                    Bug 13647938 - 3.1EA3: REGRESSION: CANNOT CANCEL PL/SQL BLOCK

                                    But please note:
                                    Ah, and one detail that can have its importance: I am not using JDBC to connect to the database (11.2.0.2 Enterprise Ed.). I use an Oracle 11.2.0.1 client for windows 32 bits, on windows 7 Enterprise SP1.
                                    No, SQL Developer always uses JDBC, it's just a matter of which version (the [thin-only] version it's packaged with, or the Oracle client install that overrides it) and whether we ask it to use Thin or Thick/OCI (via a preference, if a client is installed).

                                    SQL Developer 2.1 comes with ojdbc5 (for jdk1.5.0) and runs fine on Oracle 10g and 11g. It is an 11.1.0.6 client version.
                                    SQL Developer 3.0/3.1 come with ojdbc6 (for jdk1.6.0) and runs fine on Oracle 10g and 11g. It is an 11.2.0.2 client version.

                                    My Oracle home points to a 11.2.0.1 client version, and the ojbc6.jar in its jdbc\lib has a different size/timestamp than SQL Dev's, but both support the JDBC 4.0 API. Whether I set ORACLE_HOME to the 11.2.0.1 or 11.2.0.2 version, I see the cancel problem in 3.1 but not 3.0.
                                    So my theory about the JDBC version mismatch seems wrong. And there was a code change in the interrupt method between 3.1 and 3.0.
                                    By the way, I got the same results using both thin and thick drivers, I used jdk.1.6.0_30 with g1gc algorithm, and tested only Win XP.

                                    Thanks,
                                    Gary
                                    1 2 Previous Next