Connect to Oracle database on macOS using Instant Client
This post is part of a series of posts on how to Connect to Oracle database on macOS, in this case, using Instant Client on macOS.
We will install and use the Oracle Instant Client, which is one of the easiest and fastest ways to connect to an Oracle database, the Instant Client is practically mandatory for development environments like NodeJS, so let’s get started.
Table of Contents
☑️ Prerequisites
Before starting, you must do the following on your Mac.
- Temporarily disable macOS security (know how here)
- Download the Oracle Instant Client
Download the basic and sqlplus files according to the version of your database from the following link.Example, if you want to connect to an Oracle 19c database, you will need to install:
- instantclient-basic-macos.x64-19.3.0.0.0dbru.zip
- instantclient-sqlplus-macos.x64-19.3.0.0.0dbru.zip
Important: Oracle Call Interface 19.3 and 18.1 can connect to Oracle database 11.2 or later. If you want to connect to a lower database, you must use version 12.
⚙️ Installation
With the requirements covered, we can start the installation:
- Open terminal.
- Create the Oracle directory.
sudo mkdir -p /opt/oracle
- Move the downloaded files to the Oracle folder and unzip the files.
sudo mv instantclient-basic-macos.x64-19.3.0.0.0dbru.zip /opt/oracle/
sudo mv instantclient-sqlplus-macos.x64-19.3.0.0.0dbru.zip /opt/oracle/
cd /opt/oracle
sudo unzip instantclient-basic-macos.x64-19.3.0.0.0dbru.zip
sudo unzip instantclient-sqlplus-macos.x64-19.3.0.0.0dbru.zip
- Create the lib directory on your home and the necessary symbolic links to allow applications to find the Instant Client libraries.
mkdir ~/lib
ln -s ~/instantclient_19_3/libclntsh.dylib ~/lib/
ln -s ~/instantclient_19_3/libclntsh.dylib /usr/local/lib/
- Create the environment variables in your profile file.
echo "export PATH=/opt/oracle/instantclient_19_3:$PATH" > ~/.profile
echo "export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_3" >> ~/.profile
source ~/.profile
✅ Connect to Oracle Database
After finishing the installation, we can test the connection to the database using the command sqlplus
and one of the methods below.
Using EZCONNECT
The easiest way to connect to a database is by using EZCONNECT syntaxis.
- In terminal run the following command.
sqlplus
- Once that connect using the EZCONNECT syntax.
sqlplus user/password@//myhost.reybis.com:1521/myservicename
Using TNSNAMES
If your need to use TNSNAMES see the next steps.
- First, in terminal let’s create the TNSNAMES file.
cd /opt/oracle/instantclient_19_3/network/admin
sudo vi tnsnames.ora
- Use the following template to create your TNSNAMES file.
myservicename=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST.REYBIS.COM)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSERVICENAME)
)
)
- Then you can connect using the TNSNAMES syntax.
sqlplus user/password@myservicename
Using Oracle Wallet
Using Oracle Wallet is the safest way to access an Oracle database, if you have the wallet of your database, you just have to do the following:
cd /opt/oracle/instantclient_19_3/network/admin
unzip ~/Downloads/wallet_db.zip
sqlplus user@mywalletservicename
Oracle Autonomous Database
Oracle Cloud Autonomous databases, by default, use a secure connection with Oracle Wallet to guarantee encryption in data-transit. Oracle only allows us to connect to them using an encrypted connection to Oracle Wallet.
cd /opt/oracle/instantclient_19_3/network/admin
unzip ~/Downloads/wallet_atpdb.zip
sqlplus user/password@atpservice_high
sqlplus user/password@atpservice_medium
sqlplus user/password@atpservice_low
That’s it! You are all set to connect to an Oracle database from macOS 😃
Now I hope you enough knowledge so that you can please any requirement related to Oracle database.