Connect to Oracle database using Instant Client macOS

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
• Installation
• Connect to Oracle
  • Using EZCONNECT
  • Using TNSNAMES
  • Using Oracle Wallet
  • Oracle Autonomous Database

☑️ Prerequisites

Before starting, you must do the following on your Mac.

1. Temporarily disable macOS security (know how here)
2. 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:

1. Open terminal.
2. Create the Oracle directory.

  sudo mkdir -p /opt/oracle

1. 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

1. 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/

1. 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.

1. In terminal run the following command.

  sqlplus

1. 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.

1. First, in terminal let's create the TNSNAMES file.

  cd /opt/oracle/instantclient_19_3/network/admin
  sudo vi tnsnames.ora

1. 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)
    )
  )

1. 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.