-
Title
"Cannot find oci.dll: in" error when trying to connect. -
Description
When connecting in Optimizer, I get the error "Cannot find oci.dll: in CLI10GR2USER". This is an issue for anything I try to connect to. There are no problems connecting, using this client or to any database in other applications, including Toad.
The errors below are received in this order, but the main error is the 3rd error below:
1) "Unable to send request to Quest SQL Optimizer"
2) "Unable to establish optimization session."
3) "Cannot find OCI DLL: in DEFAULT_HOME" -
Cause
There is a limitation in a 3rd party components Toad uses to connect to Oracle. They only allow at most 10 homes in their home list populated from the HLKM\Software\Oracle registry. If the expected home is not displayed in Toad, but is an entry in the registry and valid, you may still encounter the OCI.dll error during connect.
-
Resolution
WORKAROUND 1:
Install and connect using an instant client.
Please refer to these link for details on setting up an Oracle instant client to be used with Optimizer. Note the articles refer to Toad for Oracle, but the client files, and Environmental Variables setup is the same.
How to install and configure an Oracle Instant Client for use with Toad for Oracle
https://support.quest.com/SolutionDetail.aspx?id=SOL76453How to configure TNSNAMES.ORA for use with Oracle Instant Client and Toad.
https://support.quest.com/SolutionDetail.aspx?id=SOL76454WORKAROUND 2:
**Making changes to the registry could affect the rest of your system. Please remember to backup the registry before making any changes!**
Go to HLKM\Software\Oracle\Oracle_Homes. Find and remove Oracle Home registries that may not be used and/or are invalid to reduce the number of homes.
STATUS:Waiting for fix in a future version of Quest SQL Optimizer for Oracle.WORKAROUND 3:If you are getting the "Cannot find OCI DLL: C/Oracle..." error when optimizing a SQL statement coming from Toad for Oracle, follow the steps below.Click on the "Connection" button in SQL Optimizer | choose the Oracle Client Home that is working i.e. OraClient11g_Client1 | check-mark the "Make this the default home for SQL Optimizer" | click "Create" | ensure you are able to connect. With successful connection and from there on, SQL Optimizer should now use that client home even if SQL statement is coming from another program such as Toad for Oracle. -
Defect ID
ST81520 -
Additional Information
This is a limitation with a 3rd party component so a fix for this will also depend on the component used.