Connect to Oracle database using Instant Client macOS
Tags: oracle, macos, devops, instantclient
Published: 2020-08-17 02:36:13

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](#requirements)
  * [Installation](#installation)
  * [Connect to Oracle](#connect-to-oracle)
    * [Using EZCONNECT](#using-ezconnect)
    * [Using TNSNAMES](#using-tnsnames)
    * [Using Oracle Wallet](#using-oracle-wallet)
    * [Oracle Autonomous Database](#oracle-autonomous-database)

<a name="prerequisites"></a>

### ☑️ 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](https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html).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._

<a name="installation"></a>

### ⚙️ Installation

With the requirements covered, we can start the installation:
1. Open terminal.
2. Create the Oracle directory.
  ```toml
  sudo mkdir -p /opt/oracle
  ```
  
3. Move the downloaded files to the Oracle folder and unzip the files.
  ```toml
  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
  ```

4. Create the lib directory on your home and the necessary symbolic links to allow applications to find the Instant Client libraries.
  ```toml
  mkdir ~/lib
  ln -s ~/instantclient_19_3/libclntsh.dylib ~/lib/
  ln -s ~/instantclient_19_3/libclntsh.dylib /usr/local/lib/
  ```

5. Create the environment variables in your profile file.
  ```toml
  echo "export PATH=/opt/oracle/instantclient_19_3:$PATH" > ~/.profile
  echo "export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_3" >> ~/.profile
  source ~/.profile
  ```

<a name="connect-to-oracle"></a>

### ✅ 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. 

<a name="using-ezconnect"></a>

#### Using EZCONNECT 

The easiest way to connect to a database is by using EZCONNECT syntaxis.

1. In terminal run the following command.
  ```toml
  sqlplus
  ```

2. Once that connect using the EZCONNECT syntax.
  ```toml
  sqlplus user/password@//myhost.reybis.com:1521/myservicename
  ```

<a name="using-tnsnames"></a>

#### Using TNSNAMES

If your need to use TNSNAMES see the next steps.

1. First, in terminal let's create the TNSNAMES file.
  ```toml
  cd /opt/oracle/instantclient_19_3/network/admin
  sudo vi tnsnames.ora
  ```

2. Use the following template to create your TNSNAMES file.
  ```toml
  myservicename= 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST.REYBIS.COM)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYSERVICENAME)
    )
  )
  ```

3. Then you can connect using the TNSNAMES syntax.
  ```toml
  sqlplus user/password@myservicename
  ```

<a name="using-oracle-wallet"></a>

#### 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:
  ```toml
  cd /opt/oracle/instantclient_19_3/network/admin
  unzip ~/Downloads/wallet_db.zip
  sqlplus user@mywalletservicename
  ```

<a name="oracle-autonomous-database"></a>

#### 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.
  ```toml
  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.