Using VS Code? You can take advantage of SQL Developer there as well, it also allows you to cancel your queries.
I’m not sure why, but it appears many of you wish you could take back your SQL queries. It’s like that one time you posted that picture on Facebook and immediately (3 days later) you realized it wasn’t a great career move.
Maybe that data you asked for wasn’t going to be that helpful if it took 3 hours for it to come back. This reminds me of the weather forecasting models can that could be perfect but would take years to run…anyway I’m digressing way too early already.
You run query.
You want to cancel the query.
In fact, you want it to DIE, DIE, DIE!
Let me help you kill yoursel…your query.
The Mechanics
You open connection. You submit query. We send it to the database for you. A few moments later, you change your mind and hit the ‘Cancel’ button. We let the database know you changed your mind. Maybe the database hears you and maybe it cancels the query.
It’s the ‘maybe’ part that frustrates people.
Or maybe the client sees the query as cancelled but you still see the server process (PID) running on the server. This is also alarming to folks. PMON is responsible for doing the clean-up there. That’s kind of out-of-scope for this post, but I’m going to say it’s out of the client’s (SQL Developer’s) hands. So let’s get back to the actual query being cancelled.
1. Use the Results Panel Cancel Button
Hitting this button will send the cancel request to the database. The button will activate once something’s running in the worksheet.
2. Using the Task Progress Panel
You might have stuff running in a few different worksheets and editors. You can monitor the progress of all of them using this panel. Open it from the View menu.
3. Open Monitor Sessions and Kill the Session
Find your SID, select the row, right-click, and ‘Kill Session.’ I’ve heard about folks that will just telnet into the server and issue an OS ‘kill XXXX’ to make their session go away, but I don’t recommend this as anything but a last-resort option. Let the database handle the session if at all possible, otherwise be ‘OK’ with the results.
But Why Won’t My Cancel Work?
The database is coded to listen for cancel requests in various places. Apparently for JDBC connections, it’s not listening in as many places as you may like. But, there’s a ‘fix!’
Update for Version 4 and Higher
We’ve made it easier to specify EXACTLY what Oracle HOME (client) to use for your SQL Developer connections. Read more about that here.
Anyways, once you have this sorted, start a new connection, and if you have a compatible Oracle Client on your machine, we’ll use the Oracle Call Interface (OCI) via the ocijdbc driver. Now, one of the few advantages here is that there are MORE ‘listen’ points in the database code for query cancellation requests. I’ve suggested this route for many of our users, and it seems to have done the trick for all of them.
Also, don’t forget to use ‘unshared worksheets‘ if you’re about to run a hairy query – it will run on a dedicated connection and let you use the rest of the tool without holding you up whilst you wait for it to finish.
83 Comments
> Upgrade again, to version 17.2. The way queries are cancelled in 17.2 is drastically different and you issue should be solved, with no need to use the OCI thick connections
Could you elaborate on that please?
I need to cancel statements from within my Java application (using the thin driver). And a simple Statement.cancel() does not work reliably. Are there any Oracle specific JDBC calls that make this more reliable?
Switch to a JDBC THICK connection based on your oracle home/instant client.
Thanks for the reply, but what about the “with no need to use the OCI thick connections” comment of yours?
Does this mean, I simply can get reliable cancelling with a thin connection? (Which is what Oracle recommends to use)
I mean that cancelling queries vis JDBC thin SHOULD be better than they were before, but they won’t be as good as OCI/SQLNet.
I recommend what suits your scenario best, that could be using the Thick driver.
I get the error described below as Error “testing native OCI library load … Failed” While Testing Client with SQL Developer (Doc ID 1942063.1)
but do not have an Oracle support account. Does a work around exist?
Link to the Error Doc.
https://support.oracle.com/knowledge/Middleware/1942063_1.html
Thanks.
What version of Oracle client do you have installed and what version of SQL Developer are you using?
Thanks mate. this works well for me. I was having a tough, crazy, maddening, horrible time cancelling queries.
Thanks for your reply.
SQL Developer Version 21.4.1.349 Build 349.1822
Client instantclient_19_12
Hello Thatjeffsmith,
Fully agree on Die! Or How to Cancel Queries in Oracle SQL Developer. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
Currently I’m trying to make a test using create job as a way to have
multiple process on database starting in a loop.
Basically I’m getting different behaviors when testing on different database machines. I’ll define the machine names are A and B.
If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
DECLARE
L_job_name VARCHAR2(100);
L_comments VARCHAR2(240);
L_pls_block VARCHAR2(32000);
L_thread NUMBER := 1; — count of jobs
L_max_threads NUMBER := 3; — max number of jobs to be started
L_lot_id NUMBER := 1234; — add any number just to represent a lot
BEGIN
while L_thread L_job_name,
job_type => ‘PLSQL_BLOCK’,
job_action => L_pls_block,
start_date => SYSTIMESTAMP,
comments => L_comments,
enabled => true);
–DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
L_thread := L_thread + 1;
end loop;
END;
/
but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.
Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thank you,
Krishna kumar
I tried using JDBC OCI in two different tools and canceling of long-running queries didn’t work in either. I’m using Instant Client 12.2 on Mac with Oracle 12c.
I tried:
1. Oracle SQL Developer 17.3 set to use OCI
2. JetBrains DataGrip 2017.3 with Oracle JDBC 12.2 set to use a) OCI or b) OCI8
I did the upgrade, but it still requires the thick client for query cancellation to work properly.
Something’s not right. But it’s working with the thick client?
Yes, that’s right – but version 4.1.3 *didn’t* allow cancelling of queries when using the thick client.
As version 17.2 isn’t supported within my organization I have been forced to use the JDK that is bundled with 4.1.3, so not sure if that is part of the issue …
Anyway, hopefully the feedback is helpful – thanks for your help Jeff!
It should have, but at least you’re making progress.
Still in “Task Cancelled” purgatory here, version 17.2.0.188. Doesn’t seem to matter if I use the thick / thin client – I’ve tried all permutations and it’s still really unreliable.
I have written a PowerShell script to forcibly kill SQL Developer that I can launch with a key shortcut, which mostly solves the issue.
Something isn’t right. I just don’t know what it is. If you’d like, open a Service Request with My Oracle Support and we can start to do some debugging.
I’ve been struggling with this issue on and off for years. I recently upgraded to 4.1.3 and am back in unkillable query purgatory. Nothing seems to fix it in this version – even using the OCI/Thick Client.
Attempting to cancel any long-running query usually results in having to force-quit the application – even using unshared workbooks. “Connection is currently busy. Try again?” should read “Don’t bother, please force quit the application”.
In fact, that might be a good feature: when cancelling a query, just have SQL Developer restart. It might be quicker than waiting for the Oracle driver to return. 😉
Upgrade again, to version 17.2. The way queries are cancelled in 17.2 is drastically different and you issue should be solved, with no need to use the OCI thick connections.
I started using SQL Developer today, ran into the cancel problem, and switched to the native driver as per Jeff’s advice here. It still doesn’t cancel as fast as Toad does, but I hope I can live with it. Toad costs money and it crashes a lot unfortunately. I am a little surprised that this cancel issue is not pursued more vigorously though.
It has been – with the latest 12.2 driver we can now more effectively cancel queries, so hopefully you won’t need the fat client solution anymore.
Halo,
Fully agree on Die! Or How to Cancel Queries in Oracle SQL Developer. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
Currently I’m trying to make a test using create job as a way to have multiple process on database starting in a loop.
Basically I’m getting different behaviors when testing on different database machines. I’ll define the machine names are A and B.
If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
DECLARE
L_job_name VARCHAR2(100);
L_comments VARCHAR2(240);
L_pls_block VARCHAR2(32000);
L_thread NUMBER := 1; — count of jobs
L_max_threads NUMBER := 3; — max number of jobs to be started
L_lot_id NUMBER := 1234; — add any number just to represent a lot
BEGIN
while L_thread L_job_name,
job_type => ‘PLSQL_BLOCK’,
job_action => L_pls_block,
start_date => SYSTIMESTAMP,
comments => L_comments,
enabled => true);
–DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
L_thread := L_thread + 1;
end loop;
END;
/
but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);
I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.
Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thank you,
Radhey
I think your question is a good scenario for the folks over at AskTom.
None of these work. Luckily I use VPN. So the only thing that works is to disconnect the VPN and then connect back. It is a hassle and it is frustrating, but someone with this version of my PC, my trusted PL/SQL developer does not work
SQL Developer also takes up too much memory and does not allow for keyboard shortcuts.
What doesn’t work?
How much is too much memory? You can’t compare jvm memory use to a native exe on Windows…is it too slow? Maybe the jvm needs tuned if sqldev is unresponsive.
How do you mean no KB shortcuts? We have tons of them.
I’ve tried over and over again to kill queries with SQL Developer and have never been able to get it to work, would love to get it working if possible! I have the settings above enabled, but so far nothing.
What happens for me is that the query appears to cancel (message shows ‘task cancelled in x seconds’) but it actually keeps running in the background (I can tell because if I try this with a short running query eventually it starts working again).
If I try to rerun anything (in any worksheet) in the meantime, the connection appears busy. If I try to disconnect, that doesn’t work either (sometimes nothing happens, something I get a connection busy abort/retry message). In fact, once I’ve tried to cancel a query, the only way to run another query is to end task on SQL Developer and reopen it again.
what version of sqldev and client are you running? and what version of oracle database are you on?
are these queries or are they object DML/DDL, update or alter?
SQL Dev 4.1.0, I don’t have Oracle client installed. This happens with both 11g and 12c databases. These are all select queries, I’ve never tried cancelling a DML operation.
thanks!
ok, so read this post, and implement the bits where I talk about getting an instant client and setting up thick connections. your cancel stuff should be fixed then
Apologies Jeff, I thought I already had the full Oracle client, but then I remembered I since got a new machine and hadn’t needed to install the client. I installed the instant client (the thick connections option was already enabled) but it didn’t seem to make any difference. Appreciate the help!
Jeff,
My Advanced Preferences show a ‘Display XML Value in Grid’ instead of ‘Use OCI/Thick Driver’ .
Help About shows version 4.1.0.19 Build Main-19.07.
Check for Updates shows no new updates available.
resize the dialog maybe, the use oci thick is below the display xml bit.
Same issue here I tried to resize but that option is not here. V4.1.3.20
Another thing, that make me mad — working with “Files” tab.
I keep my working files on SSD drive, but SQL Developer force me to reach a Zen, when it try to read directory structure of project.
SO it takes a long time? How many files/sudirectories are there?
Wow, I know you are trying to be sarcastic about how you handled the ‘I don’t know why you want to cancel the SQL’ help ‘Kill yoursel…sql’ things. But being a product manager, very immature. here is an enlightenment, people want kill their SQL for the same reasons multi billion dollar corps and their product managers can’t copy a product properly after several releases and keep pushing bug fixes every two weeks on an improperly implemented/copied functionality developed by another small team like TOAD developers. There are 10 more tools that are all developed by Oracle to monitor the DB but yet they can all work so clueless of each other just like your second paragraph says, left hand doesn’t know what right hand does. what’s the point of buying products from same company if they don’t integrate with each other well.
Sure, I could do what I do here w/o my personality, sarcasm, and poor sense of humour. But I’d quickly get bored. And I’d slowly stop writing so much. And people would quit reading my mindless drivel.
Also, I never claimed to be mature.
I’ve only ever wanted to help people. And if my sarcasm prevented that, I’m truly sorry.
Also, we’ve never wanted to copy a product. I’d have never taken this job if that was the goal. I’ve been on both teams, 4 years here, 10 years on TOAD. Copying is a pretty serious charge.
I know of only one monitoring solution here at Oracle, that’s Enterprise Manager.
We push releases as required. And free of charge.
If you’re happier somewhere else, don’t let me stop you from being there.
Now all that being said – were you able to get your queries to cancel in SQL Developer?
HI Jeff,
Your articles on SQL Dev are really good. We have a situation where our support team connects to Oracle 11gR2 RAC databases using SQL dev 3.0.4 (Windows terminals). We (App DBA’s) suggested to use Oracle 11gR2 client (OCI Thick drivers) instead of using JDBC Thin drivers (JDBC URL’s). From you articles we know OCI Thick drivers should be preferred way to connect to databases. We have been challenged by Tech Arch’s that JDBC thin drivers should be used only and not Oracle client. Could you please provide advantages or point to one of Oracle note, of using OCI Thick drivers (Oracle client) over OJDBC drivers (JDBC url’s).
regards
Raj
I’d rather help you get up to v4.1.2 and Java 8 first!
But
In terms of OCI, there are 2 advantages over JDBC,
The only other reasons you’d want to use a Thick connection is when your connection protocol requires it, i.e. isn’t supported in the Thin JDBC driver.
Ok Thanks.
Given the choice, should we use Oracle OCI Thick (tnsnames using Oracle 11gR2 client) connections or OJDBC thin (JDBC URL’s) connections?
regards
Raj
You don’t need any client at all to connect with tnsnames, jdbc thin supports using a tnsnames file.
What you need to do is tell me your requirements, then I can make a suggestion. If the #1 and #2 things i listed previously aren’t an issue, then it’s MUCH easier to just use straight JDBC.
HI Jeff,
We have installed SQL dev 4.1.2 with Java 8. I am trying to set Oracle client in Advanced config and upon test, I am getting error:
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
the expected native library directory C:\app\product\11.2.0\client_2\bin is present and precedes any other client installations.
java.library.path = C:\Program Files\Java\jdk1.8.0_65\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\app\product\11.2.0\client_2\bin;C:\app\product\11.2.0\client_1\bin;C:\app\lstevens_admin\product\11.2.0\client_1;C:\ProgramData\Oracle\Java\javapath;C:\Perl\site\bin;C:\Perl\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\TortoiseSVN\bin;.
My PATH setting at OS level is:
C:\app\product\11.2.0\client_2\BIN>path
PATH=C:\app\product\11.2.0\client_2\bin;C:\app\product\11.2.0\client_1\bin;C:\ap
p\lstevens_admin\product\11.2.0\client_1;C:\ProgramData\Oracle\Java\javapath;C:\
Perl\site\bin;C:\Perl\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbe
m;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\TortoiseSVN\bin
I did see your post on your SQL Dev 4 blog, but it doesnt apply to my case. Oracle Client is set and preceeds any other client installation in path settings. How do I fix this error now?
regards
Raj
Need to configure ODBC connection to Progress Database using SQL Developer. How should I do this configuration in SQL Developer?
We’re Java, so it’s JDBC only. Also, we don’t support Progress Database migrations to Oracle, so no luck there either…
Jeff,
I’ve been successfully using the OCI client for some time now without any issues. Over the past weekend while browsing with Chrome I decided to install the Java browser client which successfully installed. Now all of my attempts to connect using SQL Developer fail with the following type of error:
SQL Developer ocijdbc12.dll The specified procedure could not be found Vendor code 0
(I removed the full path to the DLL). I’m running 4.0.2.15 in 64 bit mode on Windows 7 Enterprise. I also checked my path statement and the proper BIN directory that contains this DLL is there.
The first thing that comes to mind is that when you updated the java browser client, it put its path in the ENV variable first, and SQLDev is looking there for the DLL instead of in your client folder. Make sure the PATH points to your client install path FIRST.
Jeff,
I just looked at the SQL Developer About screen’s Properties and noticed that java.library.path had the following entries which looks like the PATH but the first 3 entries are not from my PATH settings.
C:\app\sqldeveloper_4.0.2.15.21\sqldeveloper\bin;
C:\Windows\Sun\Java\bin;
C:\Windows\system32;
C:\Windows;
C:\app\Client\Jim.McCusker\product\11.2.0\client_64;
C:\app\Client\Jim.McCusker\product\11.2.0\client_64\bin;
C:\app\Client\Jim.McCusker\product\11.2.0\client_32;
C:\app\Client\Jim.McCusker\product\11.2.0\client_32\bin;
I figured it out. I had both the 11.2.0 and 12.1.0 client installed and for whatever reason my path was pointing to the 11.2.0 client. I’m now pointed to the 12.1.0. client and all is working again. Weird.
Sorry for so many replies 🙁
After some testing to see when it happens, this only seems to occur if I try to cancel the task while it is still executing, and not just fetching rows. The only way I was able to get in this case was with a large query that will always take multiple minutes to run.
Run statement. Then go to another (quick) statement and run that. Both tasks then show in task progress. Cancel the large one, but note that on the server it will keep running and that the next task will still be waiting.
Canceling a row fetch seems to work with and without OCI for me.
Any ideas of what I might be doing wrong?
I don’t think you’re doing anything wrong. It might just come back to ‘all we can do is ask the database to cancel its task’ – we can’t force a query cancellation. Normally the OCI Thick stuff remedies this situation, and it sounds like you’ve successfully set this up.
You never say what version of SQLDev you’re running, 3.2 or 4.0?
Version 4.0.0.13.80 now, but when I wrote the first comment I was using the preview. The upgrade from preview to release didn’t handle the OCI settings properly until OCI was disabled and re-enabled, but there was no difference in behavior for the complex query.
Sorry to pester, just wondering if you have any ideas about anything else I can try for this.
Using SQL*Plus I am able to cancel queries, and it seems to work properly, but I am still unable to successfully cancel any from SQLDeveloper. This is most obvious when trying to cancel a long running query, and then trying to quit the application. It tells you the connection is busy and hangs.
I have upgraded to 4.0.1.14.48 for Mac with Instant Client Version 11.2.0.3.0 (64-bit) from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
I haven’t tried using the 32-bit client, but I am using 64-bit SQLDeveloper (confirmed in Activity Monitor) so I didn’t think it was worth trying.
Have you confirmed that you have the Instant Client correctly configured with SQL Developer 4? With SQL Developer running, go to the Help > About and do a search in the properties panel for ‘oci’ – it should say that sqldeveloper.oci.available is ‘true’
Make sure you’ve enabled the thick connection preference.
If you’ve done all of this and you’re still unable to cancel queries, I would open an SR with MOS.
On my machine, SQLDeveloper still fails to properly cancel a task while set to use the OCI. OCI/Thick is selected in my preferences, but there doesn’t seem to be a way to verify that it is using OCI instead of JDBC. Any tips?
I downloaded the basic instant client for Mac (64 bit matching SQLDeveloper and version 11.2.0.3.0 matching SELECT * FROM PRODUCT_COMPONENT_VERSION). I added the path for the client to my DYLD path, and SQLDeveloper finds that and allows me to specify the path to the client (without complaining that it isn’t in the DYLD path) but the test button in the configuration does nothing with or without anything in the path.
Just saw your other post on OCI, and it does show as true in about->properties and I can reset passwords. The term “available” makes me wonder if it actually is using OCI or just knows it can.
Can you please implement .cancel for Autotrace Action?
I bet it is just not implemented as I was not able to cancel autotrace action.
I did try OCI driver and it does NOT help (the sqlplus from that ora_home is SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 10 10:17:39 2014)
Cancelling the same queries works fine from sqlplus and PLSQL Developer.
Cancel is there for Autotrace.
Just ran select * from all_source a, all_source b; through AutoTrace, the AutoTrace task bar pops up – I hit cancel…the query cancels, and then the AFTER info is gathered and the autotrace output appears.
No problem, full step by step:
1) select max(rownum) from all_source a, all_source b, all_source c, all_source d;
2) hit autotrace
3) hit cancel
expected result 3.1) UI shows the query is cancelled
expected result 3.2) the query is really cancelled at the DB side (no query in active sessions)
actual result 3.1) UI does look like the query is cancelled. For instance, the progress bar is gone and the UI allows to launch new query
actual result 3.2) the query is NOT cancelled at the database. Any attempt to launch new query _waits_ until previous autotrace finishes.
4) immediately after canceling autotrace try to execute “select * from dual”
expected result 4) the query _immediately_ returns dummy=x
actual result 4) “select * from dual” hangs forever
It looks like the UI just does not propagate cancel when using autotrace.
The same test works perfectly fine when using plain execute statement command, so NO excuse for “lack of listen points” here.
I do not mind if “cancel” will cancel autotrace completely and SQLDeveloper will not show statistics of partial executions if that reduces time-to-marked of the particular fix.
The ability to cancel long query is of top priority.
It would be great if SQLDeveloper could show statistics of partial execution (i.e. the query is cancelled immediately, and the statistics still calculated).
Didn’t realize the query itself wasn’t terminating. I’ll take another look on Monday. Thanks Vladimir for the excellent info, as always.
I don’t see ‘hangs forever’
I see the query waiting for the ‘after execution’ session stats to be gathered for the autotrace, and then the query runs.
Technically speaking it might wait till autotrace query completes.
However, the main point here is even 10 minutes is close to forever when you want it to die.
Typically I have to kill sqldeveloper from the task manager if I unexpectedly launch a long query via autotrace.
I do heavily use the unshared connection feature, but I often use ‘base connection’ by accident.
Jeff,
I’m running SQL Developer on a Windows Server 2008 Terminal Server which has the 64 bit 11.2 client installed. When I try to configure for the OCI/Thick driver (which works great on my laptop, btw) I get the following error:
oracle.jdbc.driver.T2CConnection.getLibraryVersionNumber()I
Vendor code 0
Any idea what’s going on?
Thanks,
–Jim
Running SQL Developer 4.0.0.12.84
Are you running SQL Developer as a 64 bit app, or in other words, are you running SQL Developer with a 64bit JDK?
Yes, I’m running the 64-bit version of your latest code release.
I remember now, SQL Dev 4 is shipping with an 11.2.0.3 JDBC driver, are you running a 11.2.0.3 client?
We’ll ship production with a 12 driver, so you’ll need to eventually upgrade the client to a 12 client as well.
Yes, it’s 11.2.0.3 client. Should up upgrade to the 12 drivers now or wait? Still don’t understand why I’m getting this error. Running same client on my laptop and works great.
Even without OCI I cannot connect (unless I specify the workaround)
Are you using Network Alias or Connect Identifier in your TNS defined connections? I believe the Connect Identifier will pull in OCI even if you don’t have ‘OCI Thick’ enabled in the preferences…it’s a sqlnet thing and the thin jdbc driver doesn’t support it.
I just tried using the OCI driver on a 64-bit AMD MS Terminal server and got this error: “c:\app\….\ocijdbc11.dll: Can’t load IA 32-bit .dll on a AMD 64-bit platform”.
Is there any way to get around this? I was able to use this option on my local desktop but would like to also have it available on our VM sessions.
Thanks,
–Jim
You’ll need a 64 bit Oracle client. If you already have one, make sure it’s listed first in your PATH, so SQL Developer will find and load it first over your 32 bit Home. Or, try running SQL Developer in 32 bit mode…
Thanks Jeff. Changed the path to have the 64-bit BIN directory first and that did the trick. Thanks!
–Jim
Is there another solution? Something like an AddVMoption in sqldeveloper.con that would do the trick?
I cannot change the PATH of all clients just to make sql developer work
I’d argue it works today. If you want the OCI thick connectivity, you need to make sure the right client is accessible. That’s not just us btw. I’ve been advising clients for YEARS on how to setup Winders to make it such that their applications find the right ‘Oracle Home.’
Could we add a property for you to specify the client/home? Yeah, and we’re talking about it a bit internally…
I argue it does not work. And I am not using OCI. I have both 32 and 64 clients installed, 32 first in path, and sqldev 64bits, and windows 64bits.
> how to setup Winders
wait, my applications are finding the right home, which is 32bits for most of them (default). And the 64bits applications (like Toad 12.1) as well: they look in registry not in path.
For SQLDEV4, I found the workaround :
AddVMOption -Djava.library.path=c:\oracle\product\11.2.0\client_64\bin
If you’re not using OCI, what do you care? No need for any client then.
thanks your tips,I like your blog so much
I just spent about hour with the setup, I wasn’t able to find any complete instructions.
You need to download Oracle Instant Client Basic Lite, extract the content to some folder and set the location to environment variable PATH (as the first value in PATH).
Also you need to replace ojdbc6.jar in [SQLDeveloper_HOME]\jdbc\lib\ with ojdbc6.jar from the Instant Client.
Sorry I forgot to write that it is setup of OCI/Thick driver
The Oracle Client, Instant or otherwise, does come documented with installation instructions. You should not have to replace the ojdbc6.jar file.
I simply installed 64 bit Windows client for WIN7, and toggled the preference.
With Instant Client, there’s no installer, so extract and add to PATH so it can be found.
I don’t want to install Oracle Client 11g because I’m using Oracle Client 10g and I dont want to face any issue created by installing other version of Oracle Client.
If I don’t replace the ojdbc6.jar I’m getting error when trying to establish connection in SQL Developer:
An error was encountered performing the requested operation:
oracle.jdbc.driver.T2CConnection.t2cCreateState([BI[BI[BI[BI[BI[BI[BISI[S[B[BZ[J)I
Vendor code 0
For what it’s worth – and for other’s who will be reading this discussion – you can install multiple Oracle Clients on a single machine. So you could have 11 and 10 and 9, and set it up so that your applications use the correct ones. It basically runs off of the PATH, but there are other tricks you can employ.
If you’re using 11g database, you should be using a 11g client – that’s a best practice, but I understand if that doesn’t work for you.
Jeff,
We had to do the same thing, copy the ojdbc6.jar from instantclient_11_2 into the jdbc folder of sqldeveloper to get rid of the error as well. I’m not seeing another way around this.
In version 4 you will have the ability to TELL SQL Developer which Client libraries to load. So this should get easier.