This post will demonstrate how to connect to an Oracle database from Go using the Go DRiver for ORacle (godror) and execute SQL Queries.

The code in this post is up on GitHub and was developed using Go 1.20.6 & VS Code running on Windows 10. I used a local version of the Oracle Database version 18 but as long as you know how to connect to your database it shouldn’t matter what version or where the database is running.

Set up

Install the godror driver with the following command

go get github.com/godror/godror@latest

You may also need to install Oracle Client Libraries and a good place to find out more is the Runtime requirements section of the godror project.

Hello World

The goal of the first example is to check you can connect to an Oracle database, run a SQL query and display the result.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/godror/godror"
)

func main() {

	db, err := sql.Open("godror", `user="hr" password="your_password" connectString="LocationOfOracleDBserver:1521/XEPDB1"`)

	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	row, err := db.Query("SELECT 'Hello World' FROM dual")
	if err != nil {
		log.Fatal(err)
	}
	defer row.Close()

	for row.Next() {
		var message string

		row.Scan(&message)

		fmt.Println(message)
	}
}

The first line of the main function opens a connection to the database. For this you need to provide the driver name which is “godor” and a connection string. The connection string shown here is connecting as the HR user to an Oracle Database running at the location specified by “LocationOfOracleDBserver”. A check is made that the connection is successful, if not the error details are logged.

With a connection to the database now open, a SQL query can be run and this is performed by the following line:

row, err := db.Query("SELECT 'Hello World' FROM dual")

Unsurprisingly the query returns Hello World. For brevity I won’t go into what the dual table is and why I have used it in this example, but if you want to know more this Wikipedia page is a good place to start. If you are on Oracle Database 23 and above then you can omit the from clause and use SELECT ‘Hello World’

To access the result of the query, the Next() function is called and the result is assigned to the message variable via the Scan function and then printed to the console.

As this SQL query returns one row, the loop construct isn’t necessary and the following code

for row.Next() {
		var message string

		row.Scan(&message)

		fmt.Println(message)
} 

could have been written as

var message string

row.Scan(&message)

fmt.Println(message)

The revised code gives clearer intention that the query will always return one row. I didn’t notice any performance benefits between the two methods although YMMV.

The code can now be run using the command go run name_of_your_file.go and if all goes well, the console displays

Hello World

exec: “gcc”: executable file not found in %PATH% when trying go build

When I first ran this example I saw the error message above. This Stackoverflow answer provided the solution which was my Windows environment did not have a C compiler.

Once the compiler was installed it then took my environment about 7 minutes to compile and run. Subsequent executions fortunately took a second or two.

SQL Query which returns multiple rows

This second example runs a SQL query which returns the first and last names from the employees table ordered by the last name.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/godror/godror"
)

func main() {

	db, err := sql.Open("godror", `user="hr" password="your_password" connectString="LocationOfOracleDBserver:1521/XEPDB1"`)

	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	query := "SELECT first_name, last_name FROM hr.employees ORDER BY last_name"

	employees, err := db.Query(query)
	if err != nil {
		log.Fatal(err)
	}
	defer employees.Close()

	for employees.Next() {

		var firstName string
		var lastName string

		err := employees.Scan(&firstName, &lastName)
		if err != nil {
			log.Fatal(err)
		}

		fmt.Println(firstName, lastName)
	}
}

Much of this code is similar to the first example, first a connection to the Database is made. The query variable is used to store the SQL query and this is then passed to the Query() function. As this query returns two columns, the first name and the last name, when the when the Next() function is called two string variables are declared and used to hold their respective values.

When run the names will appear in the console.

Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
... 

Acknowledgements

The Go DRiver for ORacle (godror) project.

This Stackoverflow answer for resolving the C complier issue