Can SQL Developer's Cancel Button reliably stop server‑side processing across JDBC driver versions?

Direct Answer

Oracle thin JDBC driver 12.1.0.2 and later (including 19c, 21c, 23c) reliably cancel long-running SELECT statements and most PL/SQL blocks via Statement.cancel(). Drivers 11.2.0.4 and earlier frequently ignore cancellation for PL/SQL and may only stop after the current fetch completes. The SQL Execution Timeout preference does not roll back open transactions; it only issues a client-side Statement.cancel(), leaving any transaction active until an explicit ROLLBACK or session termination.

Driver Version Reliability Breakdown

Driver Version	SELECT Cancellation	PL/SQL Cancellation	Notes
12.1.0.2+ (19c, 21c, 23c)	Reliable	Reliable for most blocks	Requires dedicated server connection; shared server/RAC may delay
11.2.0.4 and earlier	Works after current fetch	Often ignored	Inconsistent across SQL Developer releases (21.4 vs 23.1)

Transaction Behavior on Timeout/Cancel

• No automatic rollback. A cancelled statement leaves the surrounding transaction open. Any DML executed before the cancel point remains uncommitted but not rolled back.
• Autocommit caveat. If autocommit is enabled, changes made before the cancel point are already committed and cannot be rolled back by the cancel action.
• Server-side continuation. The database may continue processing until the next network round-trip, especially for PL/SQL with autonomous transactions or shared server architectures.

Verification Steps

1. Connect SQL Developer using a known driver (e.g., ojdbc8.jar 19.3.0.0).
2. Set Tools → Preferences → Database → Advanced → SQL Execution Timeout to a low value (e.g., 5 seconds).
3. Run a long-running test:
   • SELECT * FROM large_table WHERE rownum <= 1000000
   • Or a PL/SQL loop: BEGIN FOR i IN 1..100 LOOP DBMS_LOCK.SLEEP(1); END LOOP; END;
4. Click the Cancel button (or wait for timeout). Observe whether execution stops promptly.
5. Query V$SESSION for status and V$TRANSACTION for open transactions after cancel.
6. Repeat with an older driver (e.g., ojdbc6.jar 11.2.0.4) to compare.

One Diagnostic Detail Needed

Are you using a dedicated server or shared server (MTS) connection? Shared server architectures can delay or prevent cancellation until the request reaches a dispatcher, which changes the reliability expectation regardless of driver version.