Cannot connect to Oracle with OCI
If the connection to Oracle database with OCI cannot be established, try the following to resolve the problem:
When the Oracle Instant Client is installed on a separate machine, versions of the Oracle Instant Client and Oracle Server may differ. For example, Oracle Client 19.x can successfully connect to Oracle server 11.2. But the JDBC driver must have the same version as the Oracle Instant Client. Otherwise, you will see errors like Native library cannot be loaded
or Incompatible version of libocijdbc
.
For example, consider the following Oracle setup:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Oracle Instant Client 19.8.0.0
If we use the Oracle driver 19.3.0.0, we are going to get Incompatible version of libocijdbc
. But if we switch to Oracle driver 19.8.0.0, the connection will be successful. Consider the following animation.
When the Oracle Instant Client is installed on the machine with the Oracle server, the JDBC driver uses the native library to connect to the Oracle server. And this native library is a part of Oracle server software. So, the JDBC driver must have the same version as the Oracle server.
Separate machines | Same machine | |
---|---|---|
Versions of Oracle Instant client and Server | May differ | Will be the same (as the Oracle Instant Client is a part of Oracle server software) |
Version of the JDBC driver | Same as the Oracle Instant client | Same version as the Oracle server |
To find the JDBC driver version in DataGrip, do the following:
In the Data Sources and Drivers dialog (CtrlAltShift0S) , select the data source and click the Driver list and select Go to Driver to access the Drivers tab of the dialog.
In the Drivers tab, find the JDBC driver version in the Driver Files pane.
For more information about configuring the driver, refer to Configure a JDBC driver for an existing data source.
Check the versions of Oracle Instant client and Server.
The following OCI packages are required:
Basic Package
SQL*Plus Package
JDBC Supplement Package
Packages for your operating system can be downloaded from the Oracle Instant Client Downloads page at oracle.com.
The following OCI packages are required:
Basic Package
SQL*Plus Package
JDBC Supplement Package
Packages for your operating system can be downloaded from the Oracle Instant Client Downloads page at oracle.com.
Also, the oraclepki.jar file is required. You can download it from Oracle Database JDBC Driver & UCP Downloads page at oracle.com.
ZIP archive with wallet files. For more information about downloading a wallet, refer to Download Client Credentials (Wallets) at docs.oracle.com.
All the files from downloaded packages must be extracted in the same directory.
Find the directory with extracted Instant Client files (for example, ~
/Oracle ) and make sure that none of them are missing./instantclient_19_8/
Find the directory with Instant Client files (for example, ~
/Oracle )./instantclient_19_8/ Make sure that none of the files are missing.
Make sure that the oraclepki.jar is in that directory.
Make sure that the wallet files are in the wallet directory inside the network directory. The path to wallet files might look as follows: ~
/Oracle ./instantclient_19_8 /network /wallet
This is optional. With the environment variables being set, you can connect to your Oracle instance with the sqlplus tool. Note that these settings work only for the current session of the command prompt.
Check the following environment variables:
macOSWindowsexport ORACLE_HOME=~/Oracle/instantclient_19_8
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=English_America.UTF8
export PATH=$PATH:$ORACLE_HOME
set ORACLE_HOME=C:\Oracle\instantclient_19_8
set TNS_ADMIN=%ORACLE_HOME%\network\admin
set NLS_LANG=English_America.UTF8
set PATH=%PATH%:%ORACLE_HOME%
Make sure that the tnsnames.ora file is composed by using the following approach:
The file's location should look as follows: <directory_with_extracted_files>
/network , the/admin directory_with_extracted_files
is checked on Step 1.For example, ~
/Oracle ./instantclient_19_8 /network /admin The tnsnames.ora file should have the following structure:
MyTNSAlias = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db.my.domain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = my_service.my.domain.com) ) )
Consider the following example of the file:
# tnsnames.ora Network Configuration File:
{...}
The tnsnames.ora and sqlnet.ora files are included in a ZIP archive with wallet files. For more information about downloading a wallet, refer to Download Client Credentials (Wallets) at docs.oracle.com.
The files location should look as follows ~
/Oracle ./instantclient_19_8 /network /admin Open the sqlnet.ora file from the admin directory in the text editor, and make sure that the value of the
DIRECTORY
attribute is the path to your wallet. In our case, the path looks as follows:WALLET_LOCATION = (SOURCE = (METHOD = file)
{...}Consider the following screenshot of the admin directory and configuration files.
Thanks for your feedback!