Hello everyone, new to the forums here. I was hoping to find some help on an issue our devs are having. They are running Toad for Oracle Expert (x64), version 17. Whenever running the SQL optimizer tool, they get the error, "SQL Optimizer requires a valid Oracle client to be installed on your PC. You can still use Toad in 'no-client' mode." So far from what they have told me, all functions work except for the SQL optimizer. This only started happening since they moved from Toad for Oracle 11. If they go back to Toad 11, SQL optimizer works as it should. My question is, is there a software we need to download from Oracle, or are their settings we need to change? Let me know if anymore information is needed.
If this worked in Toad 11, on the same machine, then my guess is that you have a 32 bit Oracle client installed, and your new version of Toad is 64 bit. If that's true, then the new version of Toad is connecting without using the Oracle client. That's fine, but SQL Optimizer needs an Oracle client, hence the message.
If you have 64 bit Toad, try this:
- Install a 64 bit Oracle client.
- On Toad's login window, before you make your first connection, check the "Use Oracle Client" box.
Thanks for the reply John.
You are correct, issue is happening on the same machine. We went ahead and installed 64bit Oracle Client, the basic package and latest version from here (let me know if this is correct):
https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html#ic_winx64_inst
Followed the instructions and created the environment variable path as well as the TNS_ADMIN directory name. When we check the "Use Oracle Client" box, it picks up the oracle client, but when we sign in, we get a new error now:
I understand that the ORA-03113 error message basically means an issue with the connection between the client and the database. If they sign in without checking the "User Oracle Client" box, it connects successfully, but the SQL optimizer still does not work (repeats the ORA-03113).
The latest version should be fine. I just downloaded it and was able to connect with it to a 19c database without error using both Toad 17.1 and 24.2.
The 23.7 Basic package requires the latest Microsoft Visual C++ Redistributable package common for Visual Studio 2015, 2017, 2019, and 2022. Maybe you missed that?
The only other suggestion I have is you could try a slightly older client version, or a full client instead of instant. This is just something you could try. I am not saying that anything here isn't supported, but sometimes these things work better with one client version or another. I primarily use instant, and was using version 21.3 before I installed 23.7 just now.
Latest MS Visual C++ was installed, computer rebooted, no go.
I tried client version 19, but am getting the same error. Where could I find the full client version?
The 19c full client is here
https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html
21c client is here
https://www.oracle.com/database/technologies/oracle21c-windows-downloads.html
scroll down about halfway to get to the client installer. The full database installer is at the top.
Tried the 19c Client, and got this error:
So I uninstalled that one and went ahead with the 21c client. We are back to the 03113 error. Any additional environmental variables or anything in registry editor I should double check?
After you made your TNS_ADMIN env variable - did you put sqlnet.ora in that folder along with your tnsnames.ora? If not, do that.
Also, now that you have a full client with sqlplus.exe, I am curious if you can connect with sqlplus.
It normally isn't this difficult. I'm running out of ideas.
If you can connect with sqlplus.exe, the next thing I'd try is turning on Spool SQL in Toad to see if you can pinpoint what the problem is. It may be making the connection itself, or it may be some sql that is run right after connection is made.
To turn on spool sql:
- Start Toad and don't make a connection
- Close the login window
- Go to main menu -> database -> spool SQL -> spool to screen
- Now, every command that Toad sends to the database will be logged in that spool sql window
- try to make a connection
- Scroll up to the top in the spool sql output, then do a CTRL+F and look for
ORA-to find the action that caused the problem
Yes, made sure to have moved the updated sqlnet.ora and well as the tnsnames.ora files to the respective folder.
For the sqlplus, I have not attempted a connection before. I searched online and tried the following, let me know if this was the correct way:
Opened CMD, entered sqlplus
Asked for credentials, I entered the same credentials I was using to connect in Toad, received error: ORA-12560.
I am leaving the office in a bit, I will attempt the spool sql when I get the chance and respond with my findings. I appreciate your time and your help John!
at a command prompt: sqlplus user/pass@db
for example
where user/pass/db are the same values that you'd enter here:
I think maybe the DB value was incorrect (or you left it off entirely)
So, you are getting the same error using only Oracle products as you were with Toad. So we know it isn't a Toad issue.
It seems to be a problem between your Oracle client and database. I'm not sure what specifically, but now you have a case for Oracle support, and with Toad out of the loop they can't blame us. 
Sorry I couldn't solve it.
No need to apologize, I appreciate the time you took to help troubleshoot this issue! If anything, we've been able to narrow it down. I'll open a ticket request with Oracle and try to keep you updated with the outcome. Anything you'd recommend to include in the ticket that would help with a resolution?
Hm, you know, I just thought of something else...you can connect with that 32 bit client that you used with Toad 11.
If that client has SQLPlus, and you can connect using that one (easiest way to do that is to CD to that folder in CMD before typing sqlplus user/pass@db), then I would state in the case that you can connect successfully to the DB with the 32 bit client but the 64 bit client gives ORA-03113.
I would also mention the fact that you have a TNS_ADMIN env. variable specified
You might want to simplify things at this point by removing all clients but the 32 bit (working) client and the 64 bit client which gives the ORA-03113.