A common question that user’s of Oracle’s SQL Developer ask is “How can I cancel a query in SQL Developer?” Many times, the reason for this is, out of the box, SQL Developer uses a jdbc “thin” driver. This thin driver doesn’t have all of the robust functionality of the “thick” driver built into it.

The solution is to install the OCI “thick” driver for SQL Developer, although this is not a panacea either. Some queries, such as those that do remote calls over database links, are still difficult to cancel at times. Below is an easy, visual guide how to install the OCI “thick” driver for SQL Developer.

Download and unzip the “thick” driver from Oracle

Go here: http://www.oracle.com/technetwork/topics/winx64soft-089540.html

Download the appropriate version of the “Instant Client Package – Basic”

select the version of the instant client basic you want an install
Select the appropriate version of “Instant Client Package – Basic”

Next, type “Windows Key + R” to get a “Run” dialog box. Type in “C:\” (without the quotes) and hit “OK” to pull up windows explorer.

open the run dialog, navigate to the C: drive and click "OK"

Inside the C:\ drive, you’ll want to make a new folder.

Right-click in the C: drive to create a new folder.

Name it “oracle_client” (without the quotes).

Name the new folder "oracle_client"

Go to the instant client you downloaded:

Open the downloaded zip file containing the OCI client.

You’ll want to extract the contents of the zip file to the directory you just created.

Extract the contents of the zip file to your newly created file.
Extract the contents of the zip file to your newly created file.

Add to your your “PATH” environment variable

Next open up “My Computer.” How you do this will vary depending on which version of Windows you have.

Open up "My Computer" (somethinges called "Computer" or "This PC")

Right-click on an empty space somewhere in the “My Computer” window, and a context menu will pop up. Click on “Properties.”

Right Click anywhere in the empty space in My Computer and select "properties"

In the window that comes up, click “Advanced System settings”

Go to "Advanced System Settings"

Under the “Advanced” tab, choose “Environment Variables”

Click the "Advanced" tab and select "Environment Variables"

Under “System variables” choose “Path” and click the “Edit…” button.

Don’t modify any of the existing values…we just want to add a new value.

Click on the “New” button and add the path to the directory you “unzipped.” So for me, I downloaded the 11_2 driver, so what I added looks like this:

C:\oracle_client\instantclient_11_2

You’ll want to replace the instantclient_11_2 piece with whatever version of the client you’re using.

Click the “OK” button.

Click "OK"

Now click “OK” in your “Environment Variables” window.

Click "OK"

Now click “OK” in your “System Properties” window.

Click "OK"

Setting up the “thick” driver in SQL Developer

Next, you’ll want to open up SQL Developer.

Launch SQL Developer

From there, navigate to Tools -> Preferences.

You’ll want to go to “Database” -> “Advanced” in the left-hand menu bar.
From there, you’ll see a “Use Oracle Client” section. It’s important that this checkbox is checked. Once you’ve ensured this, click on the “Configure” button.

SQL Developer Preferences screen

You’ll get a dialog box. You’ll want to make sure “Instant Client” is selected from the drop-down menu and find the directory that the client was unzipped to. Again, for me, that directory was C:\oracle_client\instantclient_11_2, but for you it will likely be different, depending on which version of the client you downloaded.

Once you’ve done that, click on “Test…”

Configure Oracle Client Modal Window

You should hopefully get success messages all around:

OCI Instant client tests output

If you didn’t get success messages, you’ll need to do some troubleshooting (so make sure everything is pointing to the right directory).

Once you’ve gotten success messages for everything, click the “OK” button in your dialog.

Next, you’ll want to make sure that the “Use OCI/Thick driver” check box is checked.

SQL Developer Preferences Window

Click “OK.”

Oracle SQL Developer Preferences Window

It’ll ask you to reboot SQL Developer.

Restart SQL Developer

Once SQL Developer reboots, go to “Help” and click on “About.”

Go to Help Menu, About

Click the “Properties” tab and search for sqldeveloper.oci.available.
You should see a value of “true” for this property.

About Oracle SQL Developer properties tab sqldeveloper.oci.avail

If it doesn’t say “true” then again, you’ll need to do some additional troubleshooting here.

But if it does say “true” you’re in good shape!

Cancel Queries!

You should now have the ability to cancel a query in SQL Developer. Again, many database link queries will still refuse to cancel, but you should notice most local queries will cancel if you ask them to.

Thanks for reading!

Free Oracle SQL Tuning Tactical Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets

7 SQL Tuning Secrets You can Use Immediately, Even If You've Never Tuned A Query In Your Life

Leave a Reply

Your email address will not be published.