/ Server

Connect to Oracle Server using Golang and Go-OCI8 on Ubuntu

In this post, we'll learn about how to enable connectivity between Oracle Database and Golang on Linux Ubuntu machine.

Specific Ubuntu version we are going to use is Ubuntu 12.04.5 LTS, Precise Pangolin

Golang provided us the sql/database package, contains abstraction for RDBMS-connectivity-related purpose. We can use this package to create a connection to any RDBMS server, like MySQL, SQL Server, Oracle, or others.

However, since the package only contains abstraction stuff, we need to download the drivers for the respective database server. Connection to MySQL requires Golang MySQL driver.

The community created a lot of drivers, so we don't have to create by our self. Just choose which one is matched with the requirements.

One of the most famous RDBMS driver for Oracle is go-oci8, and in this post, we'll use it.


Table of Contents

  1. Prepare the Ubuntu Machine
  2. Set Up Golang
  3. Download the Oracle Instant Client
  4. Set Up Oracle Instant Client
  5. Connect to Oracle Server from Golang App

1. Prepare the Ubuntu Machine

We can use an actual computer with Ubuntu installed, or VM. In this post, I'll set up the VM using Vagrant, because it's insanely easy and does not require a lot of time to set up.

If you have vagrant installed on your machine, just create a new box using the command below.

$ mkdir vmlinux
$ cd vmlinux
$ vagrant init

Change the box configuration, edit the Vagrantfile file. Set the config.vm.box to "precise64".

$ nano Vagrantfile

# ...
config.vm.box = "precise64"
# ...

Run vagrant up command to start the installation process and turning on the box.

$ vagrant up

After set up is done, connect to the VM using vagrant ssh command. What this command does is pretty much create ssh connection to the box/VM.

$ vagrant ssh

OK, now you are already inside VM. Do update the synaptic package manager.

$ sudo apt-get update

2. Set Up Golang

Download latest Golang from https://golang.org/dl/; unzip the downloaded archive into /usr/local.

$ wget https://dl.google.com/go/go1.10.3.linux-amd64.tar.gz
$ sudo tar -C /usr/local -xzf go1.10.3.linux-amd64.tar.gz

Prepare new variable called $GOROOT, this variable refers to the installed Golang folder. Also, add this $GOROOT variable to $PATH.

$ echo 'export GOROOT=/usr/local/go' >> /home/vagrant/.bashrc
$ echo 'export PATH=$PATH:$GOROOT/bin' >> /home/vagrant/.bashrc

Next, create the golang workspace folder, register the path as $GOPATH, also don't forget to add it on $PATH. This folder is required by golang.

Inside this workspace, three folders should be available: bin, pkg, src.

$ mkdir /home/vagrant/goapp && cd /home/vagrant/goapp
$ mkdir bin pkg src

$ echo 'export GOPATH=/home/vagrant/goapp' >> /home/vagrant/.bashrc
$ echo 'export PATH=$PATH:$GOPATH/bin' >> /home/vagrant/.bashrc

$ source /home/vagrant/.bashrc

The $GOPATH/src will be the location of all our golang projects.

OK, now, run golang command to check whether the installation is a success or not.

$ go env

3. Download the Oracle Instant Client

Oracle Instant Client libraries provide the necessary network connectivity, as well as basic and high-end data features, to make full use of Oracle Database.

The instant client libraries can be downloaded from the Oracle website. Open link http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html, then download these three files below.

  • instantclient-basic-linux.x64-12.2.0.1.0.zip
  • instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
  • instantclient-sdk-linux.x64-12.2.0.1.0.zip

Copy the downloaded files into your Ubuntu machine.

For anyone who is using vagrant, copy the files into vmlinux/shared folder, to make it accessible from inside the VM.

4. Set Up Oracle Instant Client

Installing Oracle client is not as simple as unzipping the downloaded files. There are a few things that need to be configured properly.

First of all, install the required tools and dependencies.

$ sudo apt-get install build-essential libaio1 unzip git pkg-config

Create a new folder on ~/ called oracle, then copy the downloaded instant client files into this newly created folder.

$ mkdir -p /home/vagrant/oracle && cd /home/vagrant/oracle
$ cp /vagrant/shared/instantclient-*.zip .

Unzip the instant client zip files.

$ cd /home/vagrant/oracle
$ unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
$ unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
$ unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
$ rm -rf instantclient-*.zip

Register Oracle folder above as $ORACLE_HOME, also add it to $PATH variable.

$ echo 'export ORACLE_HOME=/home/vagrant/oracle/instantclient_12_2' >> /home/vagrant/.bashrc
$ echo 'export PATH=$PATH:$ORACLE_HOME' >> /home/vagrant/.bashrc
$ source /home/vagrant/.bashrc

Inside the $ORACLE_HOME folder, there are few files (the result from the unzipped instant client zip files). Create soft link for libclntsh.so.12.1 and libocci.so.12.1.

$ cd /home/vagrant/oracle/instantclient_12_2
$ ln -s libclntsh.so.12.1 libclntsh.so
$ ln -s libocci.so.12.1 libocci.so

Create an instant client configuration file on /etc/ld.so.conf.d/, fill it with the absolute path of $ORACLE_HOME inside this configuration file.

$ sudo sh -c 'echo '/home/vagrant/oracle/instantclient_12_2' >> /etc/ld.so.conf.d/oracle-instantclient.conf'

Then create another path variable called LD_LIBRARY_PATH, to specify directory paths that the linker should search for libraries specified.

$ echo 'export LD_LIBRARY_PATH=$ORACLE_HOME' >> /home/vagrant/.bashrc

Next, create another path variable DYLD_LIBRARY_PATH. This variable is required to make sqlplus works, and should point to the path where the unzipped instant client located, which is our $ORACLE_HOME.

$ echo 'export DYLD_LIBRARY_PATH=$ORACLE_HOME' >> /home/vagrant/.bashrc

Then execute ldconfig, to creates the necessary links and cache to the most recent shared libraries found in the directories specified on the command line (in the file /etc/ld.so.conf).

$ sudo ldconfig

Now create the oci8.pc file.

$ sudo nano /usr/lib/pkgconfig/oci8.pc

# fill with text below

instantclient=/home/vagrant/oracle/instantclient_12_2
libdir=${instantclient}
includedir=${instantclient}/sdk/include/

Name: oci8
Description: oci8 library
Version: 12.1
Libs: -L${libdir} -lclntsh
Cflags: -I${includedir}

Adjust the instantclient above, make sure it's pointing to the absolute path of $ORACLE_HOME.

OK, I think everything is pretty much done on the part setting up oracle client. Now we shall test it.

If you don't have any oracle server installed, you can get one by using this vagrant box from hilverd.

$ sqlplus system/manager@//1.2.3.4:1521/xe

5. Connect to Oracle Server from Golang App

It's pretty easy to check whether oracle client is configured properly or not, by building the go-oci8 package. So just go get the package, then build it, that's it.

$ go get -d -u github.com/mattn/go-oci8
$ cd $GOPATH/src/github.com/mattn/go-oci8
$ go build

If no error showing up then everything is good, we shall proceed to the next test.

Create new sample project on our Golang workspace, with a new file called main.go inside the project.

$ mkdir -p $GOPATH/src/test
$ cd $GOPATH/src/test
$ touch main.go

Fill the file with the code below. This example is a very simple implementation of making connection to Oracle DB server from Golang application.

// nano main.go

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-oci8"
)

func main() {
    dsn := `system/manager@//1.2.3.4:1521/xe`

    _, err := sql.Open("oci8", dsn)
    if err != nil {
        fmt.Println(err.Error())
        return
    }

    fmt.Println("successfully connected to oracle server using dsn:", dsn)
}

Test it, if no panic or error appears then everything is good.

$ go run main.go
Connect to Oracle Server using Golang and Go-OCI8 on Ubuntu
Share this