How to cancel a long running Oracle SQL query in VS Code

Some queries take longer than you want to wait of. Or, you realized you’re not asking the right question, and you need to stop and start over.

But how do you cancel a query in the Oracle SQL Developer Extension for VS Code?

You have two options from the Client perspective. And by that, I mean, in the application (VS Code), we give you two user interfaces for cancelling things.

Let’s show you how with a quick demo.

But first, I need a query that I know will take long enough for me to start a recording and then actually do a CANCEL. If you’re working with a Data Warehouse and you have end of quarter reports that can takes minutes, if not hours, then you’re not going to need any synthetic bits here.

I’m going to run a Query, that takes an input value, and then goes ‘to sleep’ for a few seconds.

PLSQL

--23ai example
-- dbms_session.sleep replaces dbms_lock_sleep;

create or replace function takes_awhile (x in integer) return BOOLEAN
IS
BEGIN
    dbms_session.sleep(x);
    return true;
EXCEPTION
 when others then 
  return false;
end takes_awhile;
/


--no FROM DUAL required
select takes_awhile(25);