This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 17, 2012 4:52 PM by Gary Graham RSS

3.1EA1 Can not Cancel PLSQL Block

AM Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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- Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    RDBMS info please?
  • 6. Re: 3.1EA1 Can not Cancel PLSQL Block
    AM Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Is this a bug or something with my environment ?
  • 8. Re: 3.1EA1 Can not Cancel PLSQL Block
    Vadim Tropashko Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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