Blog

Victor is a full stack software engineer who loves travelling and building things. Most recently created Ewolo, a cross-platform workout logger.

    Connecting to an Oracle database with Golang on Ubuntu

    I recently had the pleasure of updating an old PHP script that was pulling data from an Oracle database into a fully fledged web application. The team decided to use Go and one of the first hurdles that needed to be crossed was getting a db connection going.

    In this tutorial we will look at installing a Golang Oracle driver. Note that before we start we need to have the oracle client and sdk already setup on the system. The following instructions work for Ubuntu 18.04 and above.

    Following the setup described above, the oracle client and sdk should have been installed under /opt/oracle/instantclient_18_3 and /opt/oracle/instantclient_18_3/sdk respectively. Let us first try and install the most popular Golang oracle driver, go-oci8:

    $ go get github.com/mattn/go-oci8
    # pkg-config --cflags oci8
    Package oci8 was not found in the pkg-config search path.
    Perhaps you should add the directory containing `oci8.pc'
    to the PKG_CONFIG_PATH environment variable
    No package 'oci8' found
    exit status 1
    

    As the error suggests, pkg-config, which provisions details for compiling and linking a program to a library could not find the oci8 configuration information. pkg-config should be already installed on your system, but if not you can do so with sudo apt install build-essential pkg-config. Thus, we can create the following configuration file sudo vim /usr/lib/pkgconfig/oci8.pc:

    prefixdir=/opt/oracle/instantclient_18_3
    libdir=${prefixdir}
    includedir=${prefixdir}/sdk/include
    
    Name: OCI
    Description: Oracle database driver
    Version: 18.3
    Libs: -L${libdir} -lclntsh
    Cflags: -I${includedir}
    

    Note the values of the prefixdir and includedir in case the oracle instant client location is different on your system. We can now install the Golang module again: go get github.com/mattn/go-oci8.

    Here's a sample program to test the database connection:

    package main
    
    import (
    	"database/sql"
    	"fmt"
    	"log"
    
    	_ "github.com/mattn/go-oci8"
    )
    
    func main() {
    	db, err := sql.Open("oci8", "user/pass@dsn")
    	if err != nil {
    		log.Fatal(err)
    	}
    	defer db.Close()
    
    	if err = db.Ping(); err != nil {
    		log.Fatal("Error connecting to the database\n", err)
    	}
    
    	queryParam := `%ictor%`
    	rows, err := db.Query("SELECT email FROM users WHERE name LIKE :1", &queryParam)
    	if err != nil {
    		log.Fatal("Error fetching user data\n", err)
    	}
    	defer rows.Close()
    
    	for rows.Next() {
    		var name string
    		rows.Scan(&name)
    		fmt.Println(name)
    	}
    }
    

    Don't forget to modify the query in the above program to suit your needs. Maximum credit goes to Trent for the initial tutorial. Happy coding!

    HackerNews submission / discussion

    Back to the article list.

    SmallData newsletter

    Subscribe to get articles as they are published direct to your inbox!